Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jughead1111
Starting Member
14 Posts |
Posted - 2007-10-04 : 17:01:02
|
I'm writing a procedure for calculating the fiscal periods in a 4-4-5 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 belowALTER 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 ASBEGIN -- 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 offsetsdeclare @fy_ofs_date datetimedeclare @fy_ofs_year intdeclare @fy_ofs_period int-- Variables for determining days in Jandeclare @FirstDayOfYear datetimedeclare @FirstFriday datetimedeclare @LastDayInJan datetimedeclare @LastDayofYear datetimedeclare @FirstPeriodDays int-- Variable for loopdeclare @dloop datetime-- Variables to hold calculated valuesdeclare @period intdeclare @year intdeclare @end datetime/****************************************************************************/-- Initialize variables/****************************************************************************/--First determine how many days are in the first period since it differs each yearselect @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 parametersselect @fy_year = @yearselect @fy_period = @periodselect @mtd_start = @dloop-- Set last day of periodif @period = 1 begin begin select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start) end endelse 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 = @datewhile datepart(dw,@dloop) <> 1 begin select @dloop = dateadd(d,-1,@dloop) endselect @wtd_start = @dloopselect @wtd_end = dateadd(d,6,@wtd_start)END |
|
jughead1111
Starting Member
14 Posts |
Posted - 2007-10-05 : 14:57:19
|
Never mind...I figured the problem out. If anyone is interested in the codeALTER 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 ASBEGIN -- 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 offsetsdeclare @fy_ofs_date datetimedeclare @fy_ofs_year intdeclare @fy_ofs_period int-- Variables for determining days in Jandeclare @FirstDayOfYear datetimedeclare @FirstFriday datetimedeclare @LastDayInJan datetimedeclare @LastDayofYear datetimedeclare @FirstPeriodDays int-- Variable for loopdeclare @dloop datetime-- Variables to hold calculated valuesdeclare @period intdeclare @year intdeclare @end datetime/****************************************************************************/-- Initialize variables/****************************************************************************/--First determine how many days are in the first period since it differs each yearselect @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 parametersselect @fy_year = @yearselect @fy_period = @periodselect @mtd_start = @dloop-- Set last day of periodif @period = 12 begin select @mtd_end = convert(datetime, '12/31/' + convert(char(4), datepart(yyyy, @date)), 120) endelsebeginif @period = 1 begin select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start) endelseBeginif @period = 3 or @period = 6 or @period = 9 begin select @mtd_end = dateadd(d,34,@mtd_start) endelse begin select @mtd_end = dateadd(d,27,@mtd_start) endendend-- Set week to date range (loop backwards to find start of week - Sunday)select @dloop = @datewhile datepart(dw,@dloop) <> 1 begin select @dloop = dateadd(d,-1,@dloop) endselect @wtd_start = @dloopselect @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)
7020 Posts |
Posted - 2007-10-05 : 15:45:57
|
You didn't really explain what a "4-4-5 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 - 2007-10-05 : 16:05:57
|
I apologize for the confusion. A 4-4-5 calendar is a fiscal calendar where month is a certain number of weeks. Month 1 - 4 weeksMonth 2 - 4 weeksMonth 3 - 5 weeksMonth 4 - 4 weeksMonth 5 - 4 weeksMonth 6 - 5 weeksMonth 7 - 4 weeksMonth 8 - 4 weeksMonth 9 - 5 weeksMonth 10 - 4 weeksMonth 11 - 4 weeksMonth 12 - 5 weeksYou can start on any day. My company just happens to start on 1/1/yyyy and end on 12/31/yyyyMy procedure above attempts to figure out a given month start, month end, week start, week end, period (1-12), 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)
7020 Posts |
Posted - 2007-10-05 : 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_YEAR-1)*7,FY_START), FY_WEEK_END = case when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END then FY_END else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) endfrom(Select aaaa.*, WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1from(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 - 2007-10-09 : 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
30421 Posts |
Posted - 2007-10-09 : 09:04:27
|
quote: Originally posted by jughead1111 Month 1 - 4 weeksMonth 2 - 4 weeksMonth 3 - 5 weeksMonth 4 - 4 weeksMonth 5 - 4 weeksMonth 6 - 5 weeksMonth 7 - 4 weeksMonth 8 - 4 weeksMonth 9 - 5 weeksMonth 10 - 4 weeksMonth 11 - 4 weeksMonth 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 - 2007-10-09 : 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 Post |
Posted - 2012-09-29 : 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_YEAR-1)*7,FY_START), FY_WEEK_END = case when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END then FY_END else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) endfrom(Select aaaa.*, WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1from(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 4-4-5 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
9 Posts |
Posted - 2012-11-30 : 16:52:34
|
I know the post is old but I wanted to point out the error with it. A 4-4-5 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 4-4-5, 4-4-5, 4-4-5, 4-4-6 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_YEAR-1)*7,FY_START), FY_WEEK_END = case when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END then FY_END else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) endfrom(Select aaaa.*, WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1from(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)
7020 Posts |
Posted - 2012-12-01 : 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 4-4-5 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 4-4-5, 4-4-5, 4-4-5, 4-4-6 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
8 Posts |
Posted - 2012-12-02 : 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. |
|
|
|
|
|
|
|