Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old December 31st, 2010, 11:01 AM
joshk joshk is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2006
Posts: 7 joshk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 17 sec
Reputation Power: 0
Unhappy Financial Period Calculation Function is Suddenly Off

I hope I can explain this clearly -- our company uses a financial period calendar (13 4-week periods) instead of a monthly calendar. I have several functions that calculate when a given period begins or ends, what period we're in today, etc. Everything was fine until this past Sunday (12/26/10) when all of a sudden everything from that date forward is off by a week.

For example, period 12 of this year began on 11/28 and ended on 12/25. The beginning date is shown accurately but the ending date is showing as 1/1/11. Everything prior to 12/26/10 displays correctly and everything from 12/26/10 on is a week behind. Basically period 13 of this year is showing as being five weeks long, which is throwing everything after that date off.

A sample function is below. Can anyone assist me in finding what might be causing this to happen? I'm sure it's some flaw in the logic but I didn't write the functions and I don't know where to begin.

FUNCTION 1:

ALTER FUNCTION [dbo].[GetPeriod]
(@Date datetime)
RETURNS int
AS
BEGIN
DECLARE
@returnPeriod int
SET @returnPeriod = floor((datediff(dd, DATEADD(day, floor(datediff(dd, '12/31/2000', @date)/1820)*7, '12/31/2000'), @date) % 364)/28)+1


return (@returnPeriod)
end

FUNCTION 2:

ALTER FUNCTION [dbo].[GetPeriodBeginDate]
( @PeriodYear int,
@Period int )
RETURNS datetime
AS
BEGIN
DECLARE
@LeapPeriods int,
@returnDate datetime,
@add int
set @returnDate = '12/31/2000'
set @period = 13*(@periodYear - 2001) + (@period - 1)
set @LeapPeriods = FLOOR(@period / 65)
set @add = @LeapPeriods * 7
set @returndate = DATEADD(dd,(@period * 28) + @add, @returnDate)


return (@returnDate)
end

FUNCTION 3:

ALTER FUNCTION [dbo].[GetPeriodEndDate]
( @PeriodYear int,
@Period int )
RETURNS datetime
AS
BEGIN
DECLARE
@LeapPeriods int,
@returnDate datetime,
@add int
set @returnDate = '12/30/2000'
set @period = 13*(@periodYear - 2001) + @period
set @LeapPeriods = FLOOR(@period / 65)
set @add = @LeapPeriods * 7
set @returndate = DATEADD(dd,(@period * 28) + @add, @returnDate)


return (@returnDate)
end

FUNCTION 4:

ALTER FUNCTION [dbo].[GetPeriodYear]
(@Date datetime)
RETURNS int
AS
BEGIN
DECLARE
@returnPeriodYear int
SET @returnPeriodYear = 2001 + floor(floor(datediff(dd, DATEADD(day, floor(datediff(dd, '12/31/2000', @date)/1820)*7, '12/31/2000'), @date)/28)/13)


return (@returnPeriodYear)
end

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > Financial Period Calculation Function is Suddenly Off


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap