Creating a 4-4-5 Time Period table

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),

dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),

'next_startdate'=dateadd(day,86@.dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint

set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint

set @.i = 0

while @.i < 9

begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers

set @.i = @.i + 1

end

delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)

|||

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

|||

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

|||

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

|||

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iTemp int

This is the table we'll populate and return at the end

declare @.tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @.dtFiscalYearStart = 'December 31, 2006'

set @.dtFiscalYearEnd = 'December 29, 2007'

set @.iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)

begin

insert into @.tb (CalendarDate, FiscalWeek)

select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1

set @.iTEmp = @.iTemp + 1

end

update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth

select * from @.tb

|||

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

|||

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

|||

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

|||

I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.

|||

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @.dtFiscalYearEnd

to this:

CalendarDate > @.dtFiscalYearEnd

|||

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

|||

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!