Author 
Topic 

jughead1111
Starting Member
14 Posts 
Posted  10/04/2007 : 17:01:02

I'm writing a procedure for calculating the fiscal periods in a 445 calendar. We use a calendar year for our fiscal year. Therefore, the 1st period (January) is always a different number of days.
I have finished most of the script but when I put in a date other than a January date it gives an endless loop.
Could someone take a look and see where my mistake is? Code is below
ALTER PROCEDURE [dbo].[spJCTFiscalCalc]  Add the parameters for the stored procedure here @date datetime,  The date being sent to the procedure @fy_year int output, @fy_period int output, @wtd_start datetime output, @wtd_end datetime output, @mtd_start datetime output, @mtd_end datetime output AS BEGIN  SET NOCOUNT ON added to prevent extra result sets from  interfering with SELECT statements. SET NOCOUNT ON;
 Insert statements for procedure here /****************************************************************************/  Declare variables /****************************************************************************/  Starting fiscal offsets declare @fy_ofs_date datetime declare @fy_ofs_year int declare @fy_ofs_period int
 Variables for determining days in Jan declare @FirstDayOfYear datetime declare @FirstFriday datetime declare @LastDayInJan datetime declare @LastDayofYear datetime declare @FirstPeriodDays int
 Variable for loop declare @dloop datetime
 Variables to hold calculated values declare @period int declare @year int declare @end datetime
/****************************************************************************/  Initialize variables /****************************************************************************/
First determine how many days are in the first period since it differs each year select @FirstDayOfYear = Convert(char(10), '1/1/' + Convert(char(4), datepart(yyyy, @date)), 120) Check to see if its Friday if datepart(dw, @FirstDayOfYear) = 6 select @FirstFriday = @FirstDayOfYear Check to see if its Saturday if datepart(dw, @FirstDayOfYear) = 7 select @FirstFriday = DateAdd(d, 6, @FirstDayOfYear) else select @FirstFriday = DateAdd(d, 0(datepart(dw, @FirstDayOfYear)6), @FirstDayOfYear)
select @LastDayInJan = DateAdd(wk, 3, @FirstFriday) select @FirstPeriodDays = DateDiff(d, @FirstDayOfYear, @LastDayInJan)
 Set the beginning offsets for FY 2005. We will count forward or  backward from this point to get our information. select @fy_ofs_date = @FirstDayOfYear select @fy_ofs_year = Convert(int, datepart(yyyy, @date)) select @fy_ofs_period = 1
 Is this date in the past or future? if datediff(d,@fy_ofs_date,@date) >= 0 begin  Start at the offset and work up until we reach our date select @dloop = @fy_ofs_date select @period = 1 select @year = @fy_ofs_year  Loop through dates while @dloop <= @date begin  Break out of loop if the next increment will  put us over the selected date if @period = 1 begin if dateadd(d, @FirstPeriodDays, @dloop) > @date begin break end end else begin if @period = 3 or @period = 6 or @period = 9 or @period = 12 begin if dateadd(d,35,@dloop) > @date begin break end end else begin if dateadd(d,27,@dloop) >= @date begin break end end end  Increment according to period if @period = 1 begin if dateadd(d, @FirstPeriodDays, @dloop) > @date begin break end end else begin if @period = 3 or @period = 6 or @period = 9 or @period = 12 begin select @dloop = dateadd(d,35,@dloop) end else begin select @dloop = dateadd(d,27,@dloop)+1 end  Increment period number if @period = 12 begin select @period = 1 select @year = @year + 1 end else begin select @period = @period + 1 end end end end
 Put results in output parameters select @fy_year = @year select @fy_period = @period select @mtd_start = @dloop
 Set last day of period if @period = 1 begin begin select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start) end end else begin if @period = 3 or @period = 6 or @period = 9 or @period = 12 begin select @mtd_end = dateadd(d,34,@mtd_start) end else begin select @mtd_end = dateadd(d,27,@mtd_start) end end  Set week to date range (loop backwards to find start of week  Sunday) select @dloop = @date while datepart(dw,@dloop) <> 1 begin select @dloop = dateadd(d,1,@dloop) end select @wtd_start = @dloop select @wtd_end = dateadd(d,6,@wtd_start)
END 

jughead1111
Starting Member
14 Posts 
Posted  10/05/2007 : 14:57:19

Never mind...I figured the problem out. If anyone is interested in the code
ALTER PROCEDURE [dbo].[spJCTFiscalCalc]
 Add the parameters for the stored procedure here
@date datetime,  The date being sent to the procedure
@fy_year int output,
@fy_period int output,
@wtd_start datetime output,
@wtd_end datetime output,
@mtd_start datetime output,
@mtd_end datetime output
AS
BEGIN
 SET NOCOUNT ON added to prevent extra result sets from
 interfering with SELECT statements.
SET NOCOUNT ON;
 Insert statements for procedure here
/****************************************************************************/
 Declare variables
/****************************************************************************/
 Starting fiscal offsets
declare @fy_ofs_date datetime
declare @fy_ofs_year int
declare @fy_ofs_period int
 Variables for determining days in Jan
declare @FirstDayOfYear datetime
declare @FirstFriday datetime
declare @LastDayInJan datetime
declare @LastDayofYear datetime
declare @FirstPeriodDays int
 Variable for loop
declare @dloop datetime
 Variables to hold calculated values
declare @period int
declare @year int
declare @end datetime
/****************************************************************************/
 Initialize variables
/****************************************************************************/
First determine how many days are in the first period since it differs each year
select @FirstDayOfYear = Convert(char(10), '1/1/' + Convert(char(4), datepart(yyyy, @date)), 120)
Check to see if its Friday
if datepart(dw, @FirstDayOfYear) = 6
select @FirstFriday = @FirstDayOfYear
Check to see if its Saturday
if datepart(dw, @FirstDayOfYear) = 7
select @FirstFriday = DateAdd(d, 6, @FirstDayOfYear)
else
select @FirstFriday = DateAdd(d, 0(datepart(dw, @FirstDayOfYear)6), @FirstDayOfYear)
select @LastDayInJan = DateAdd(wk, 3, @FirstFriday)
select @FirstPeriodDays = DateDiff(d, @FirstDayOfYear, @LastDayInJan)
select @fy_ofs_date = @FirstDayOfYear
select @fy_ofs_year = Convert(int, datepart(yyyy, @date))
select @fy_ofs_period = 1
if period = 1
 begin
 if dateadd(d, @FirstPeriodDays, @dloop) > @date
 end
 Is this date in the past or future?
if datediff(d,@fy_ofs_date,@date) >= 0
begin 1
 Start at the offset and work up until we reach our date
select @dloop = @fy_ofs_date
select @period = 1
select @year = @fy_ofs_year
if @period = 1
begin
if dateadd(d, @FirstPeriodDays, @dloop) > @date
begin
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop
end
else
begin
select @dloop = dateadd(d, @FirstPeriodDays, @dloop) +1
select @period = @period + 1
end
 Loop through dates
while @dloop <= @date
begin 2
 Break out of loop if the next increment will
 put us over the selected date
if @period = 12
begin
break
end
else
begin
if @period = 3 or @period = 6 or @period = 9
begin
if dateadd(d,35,@dloop) > @date
begin
break
end
end
else
begin
if dateadd(d,27,@dloop) >= @date
begin
break
end
end
 Increment according to period
if @period = 3 or @period = 6 or @period = 9
begin
select @dloop = dateadd(d,35,@dloop)
end
else
begin
select @dloop = dateadd(d,27,@dloop)+1
end
 Increment period number
if @period = 12
begin
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop
end
else
begin
select @period = @period + 1
end
end 2
end
end 1
end
 Put results in output parameters
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop
 Set last day of period
if @period = 12
begin
select @mtd_end = convert(datetime, '12/31/' + convert(char(4), datepart(yyyy, @date)), 120)
end
else
begin
if @period = 1
begin
select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start)
end
else
Begin
if @period = 3 or @period = 6 or @period = 9
begin
select @mtd_end = dateadd(d,34,@mtd_start)
end
else
begin
select @mtd_end = dateadd(d,27,@mtd_start)
end
end
end
 Set week to date range (loop backwards to find start of week  Sunday)
select @dloop = @date
while datepart(dw,@dloop) <> 1
begin
select @dloop = dateadd(d,1,@dloop)
end
select @wtd_start = @dloop
select @wtd_end = dateadd(d,6,@wtd_start)
END
I would be appreaciated if anyone could add any thoughts or suggestions on how to improve this code. 


Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  10/05/2007 : 15:45:57

You didn't really explain what a "445 calendar" is, so it is hard to comment on what you are trying to so, or if there is a simpler way.
CODO ERGO SUM 


jughead1111
Starting Member
14 Posts 
Posted  10/05/2007 : 16:05:57

I apologize for the confusion. A 445 calendar is a fiscal calendar where month is a certain number of weeks.
Month 1  4 weeks Month 2  4 weeks Month 3  5 weeks Month 4  4 weeks Month 5  4 weeks Month 6  5 weeks Month 7  4 weeks Month 8  4 weeks Month 9  5 weeks Month 10  4 weeks Month 11  4 weeks Month 12  5 weeks
You can start on any day. My company just happens to start on 1/1/yyyy and end on 12/31/yyyy
My procedure above attempts to figure out a given month start, month end, week start, week end, period (112), and fiscal year based on a date passed to the procedure.
I know a table could be used for the data and could be used to lookup the dates, but I thought this might be a better solution.
I hope this clears up the confusion.
I hope this helps



Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  10/05/2007 : 17:46:50

This code does it with a single select statement.
You can turn it into a stored procedure it you want.
select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,1,b.date),1)
from
(
select
 Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
 Function available on this link
 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa
CODO ERGO SUM 


jughead1111
Starting Member
14 Posts 
Posted  10/09/2007 : 08:19:31

I will look at the results more closely, but upon first glance it looks like I have a lot to learn.
There are always more than one way to skin the cat.
Thanks



SwePeso
Patron Saint of Lost Yaks
Sweden
30114 Posts 
Posted  10/09/2007 : 09:04:27

quote: Originally posted by jughead1111
Month 1  4 weeks Month 2  4 weeks Month 3  5 weeks Month 4  4 weeks Month 5  4 weeks Month 6  5 weeks Month 7  4 weeks Month 8  4 weeks Month 9  5 weeks Month 10  4 weeks Month 11  4 weeks Month 12  5 weeks
Where do you put the 1 or 2 remaining days?
E 12°55'05.25" N 56°04'39.16" 


jughead1111
Starting Member
14 Posts 
Posted  10/09/2007 : 09:26:05

Our fiscal is always 1/1/yyyy and 12/31/yyyy. The remaining days in December is always counted in the last period 12. When you use calendar year like this the number of days in the first period (January) and the last period (December) fluctuates year to year.
In 2006 period 1 had 27 days and period 12 had 37 days. In 2007 period 1 had 26 days and period 12 will have 38 days.



maddog
Starting Member
1 Posts 
Posted  09/29/2012 : 10:36:43

quote: Originally posted by Michael Valentine Jones
This code does it with a single select statement.
You can turn it into a stored procedure it you want.
select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,1,b.date),1)
from
(
select
 Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
 Function available on this link
 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa
CODO ERGO SUM
This reply is for MVJ  I know this is an old post, but am looking around for a SQL 2000 (yes I know) solution to calculate an annual 445 calendar with a fixed Jan 1 and Dec 31 fiscal year start and end date, but with a 'week' being Saturday through Friday versus Sunday through Saturday. So in the SQL example above, what would need to be adjusted to make the starting day of the week shift to Saturday? I played around a bit with the dateadd functions at the bottom but was not successful. Cheers! 


hoghunter
Starting Member
USA
9 Posts 
Posted  11/30/2012 : 16:52:34

I know the post is old but I wanted to point out the error with it. A 445 fiscal calendar doesn't always start on 1/1. It starts one day after the previous year ended. The rule is a year mus end within the calendar year and not have more than 7 days remaining in the calendar year. So if a year started on say 12/27/1998 as ours did in 1998, it would end on 12/25/1999. Then a fiscal start of 12/26/1999 would force that year to end on 12/25/2000 which leave 7 days. So that you was a 445, 445, 445, 446 year. It ended 12/30/2000.
So your computation needs to be able to account for the fiscal year starting on a different day than Jan 1.
quote: Originally posted by Michael Valentine Jones
This code does it with a single select statement.
You can turn it into a stored procedure it you want.
select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,1,b.date),1)
from
(
select
 Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
 Function available on this link
 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa
CODO ERGO SUM



Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  12/01/2012 : 00:09:24

quote: Originally posted by hoghunter
I know the post is old but I wanted to point out the error with it. A 445 fiscal calendar doesn't always start on 1/1. It starts one day after the previous year ended. The rule is a year mus end within the calendar year and not have more than 7 days remaining in the calendar year. So if a year started on say 12/27/1998 as ours did in 1998, it would end on 12/25/1999. Then a fiscal start of 12/26/1999 would force that year to end on 12/25/2000 which leave 7 days. So that you was a 445, 445, 445, 446 year. It ended 12/30/2000.
So your computation needs to be able to account for the fiscal year starting on a different day than Jan 1. ...
Every organization has different rules. The OP said "...We use a calendar year for our fiscal year...", so their fiscal year starts on Jan 1 and ends on Dec 31.
My post was meant for the OP, not for the way you organization does things.
CODO ERGO SUM 


Howard43Willard
Starting Member
USA
8 Posts 
Posted  12/02/2012 : 20:20:11

I have finished most of the script but when I put in a date other than a January date it gives an endless loop.

Edited by  Howard43Willard on 12/02/2012 20:22:27 



Topic 


