SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Cliff Corder / 4-4-5 Calendar Functions, Part 1 / Latest Posts

RE: 4-4-5 Calendar Functions, Part 1

maddogs — Wed, 03 Oct 2012 07:27:47 GMT

Sounds great, thanks :-)

RE: 4-4-5 Calendar Functions, Part 1

Jeff Moden — Wed, 03 Oct 2012 05:16:34 GMT

Computers have made most date calculations a breeze. I'm amazed that companies and organizations are still stuck with the idea of week-based calculations that require such nuances as ISO weeks, 4-4-5 and 4-5-4 calculations.

RE: 4-4-5 Calendar Functions, Part 1

Gagne — Tue, 02 Oct 2012 19:43:26 GMT

Yes of course, I forgot they were not SQL 2000 functions. I'm gonna check on my machine tomorrow, maybe I still have the 2000 code for this.

RE: 4-4-5 Calendar Functions, Part 1

maddogs — Tue, 02 Oct 2012 18:05:54 GMT

Thanks for responding, and so quickly!The 'dense_rank()' and 'over(partition by' bits in the stored procedure are not functions in SQL 2000, and I don't have use of Analysis Services either unfortunately.Thanks,

RE: 4-4-5 Calendar Functions, Part 1

Gagne — Tue, 02 Oct 2012 17:57:21 GMT

Hi Maddog.I no longer have access to a SQL 2000 server to test but what's not working in my scrupt ? I reviewed it quickly and I think everything should work besides the table variables but that can replaced wiith temp tables.Can you run the code and show us what errors you get ?

RE: 4-4-5 Calendar Functions, Part 1

maddogs — Tue, 02 Oct 2012 17:48:13 GMT

Gagne - I know this post is old but I came across it looking for a 4-4-5 fiscal calendar generator with exactly the criteria you have (Jan1-Dec31 fiscal year, periods end on Friday, etc.) but unfortunately we are still stuck with SQL 2000. Do you have a version of this code that works with SQL 2000?Thanks,Dennis

RE: 4-4-5 Calendar Functions, Part 1

Dr. Bob — Mon, 06 Sep 2010 11:24:37 GMT

Cliff:I don't know if it was mentioned earlier, but many retail related businesses use a 4-5-4 calendar determined by the National Retail Federation (http://www.nrf.com/modules.php?name=pages&sp_id=391). Hence the division into 4-5-4 or 4-4-5 is not arbitrary. There are many anomalies that occur over the years and how they are handled is determined by this group. Hence the only easy way to handle it is by a look-up table for organizations using the NRF calendar.Regards

RE: 4-4-5 Calendar Functions, Part 1

corder — Mon, 06 Sep 2010 07:57:15 GMT

There sure are a lot of variations on this idea. The accountants are even trickery than I thought. :)

RE: 4-4-5 Calendar Functions, Part 1

brewmanz — Mon, 06 Sep 2010 05:02:39 GMT

[quote][b]ian.fickling (9/6/2010)[/b][hr]Still not clear to me how you have resolved the week on week comparison.[/quote]Each month has field which states how many weeks it has, this year and last (4 or 5)There are potentially 60 week slots (5 per month), but only 52 or 53 are used in any year.Each week-ending date has a month number (1-12 and decided by where Wednesday [YMMV!] falls) and week-in-month number (1-5); it is also allocated a week number in the range 1-60 (yes, there are gaps)It also has 'last year figure' filled with corresponding week in previous year (with 4-to-5 and 5-to-4 being pro rata)The system uses weekly comparisons.It also handles quite nicely the nasty 5-4-5 that pops up occasionally that gives the 53 week year. It also often gives quarters of 4-5-4 and 5-5-4 (giving nice month boundaries) rather than the traditional 4-4-5 (which can have a week completely in the wrong month).Now I just a method to adjust for Easter trading ...

RE: 4-4-5 Calendar Functions, Part 1

ian.fickling — Mon, 06 Sep 2010 04:45:13 GMT

Thanks for your response Brewmanz.Interesting concept - 60 week calendar - thought I'd seen it all.My understanding of what you've implemented is a method of smoothing values based on a 4 week to 5 week period comparison.Still not clear to me how you have resolved the week on week comparison.Cheers

RE: 4-4-5 Calendar Functions, Part 1

Athos Athanasiou — Mon, 06 Sep 2010 04:19:29 GMT

Excellent article.Although I found it difficult to read because you were using American date format both in the explanation and the code. Surely it should always be best practice to use universal dates, yyyy-mm-dd, when passing date strings around otherwise as soon as you send your script to Europe all the dates are wrong.

RE: 4-4-5 Calendar Functions, Part 1

brewmanz — Mon, 06 Sep 2010 01:40:09 GMT

[quote][b]ian.fickling (9/6/2010)[/b][hr]...As Cliff Corder mentioned earlier the diggest difficulty with 445/444 is that you end up with 364 days a year. Each year your calendar drifts a day eventually after 7 years what was week1 does not match a like for like date of week1 the previous year....When week1 2010 is compared to week1 of 2009, one is not comparing like for like dates.This causes lots of complications in which we had to introduce a new 'compare to' type columns.I was wondering how other people have coped with this issue.[/quote]I use a 60-week year (each month has 5 weeks). For comparison purposes, each week has a 'last year' value, and does a pro-rata between years where a month switches from 4 to 5 or 5 to 4 week months. e.g. The ratios for 4-to-5 are this[1]= last[1], this[2]= last[1]*0.25+last[2]*0.75, this[3] = last[2]*0.5 + last[3]*0.5, this[4] = last[3]*0.75 + last[4]*0.25, this[5]=last[4]hope that helpsBrewmanz

RE: 4-4-5 Calendar Functions, Part 1

ian.fickling — Mon, 06 Sep 2010 01:04:20 GMT

Hi I agree with Joe on this one.We run a Business Object reporting application in which we deal with many permutatiions of calendar controls. Even 13 period calednars using 444 weeks.Having a series of calendar maintenace procedures to generate the base calendar tables is required.As Cliff Corder mentioned earlier the diggest difficulty with 445/444 is that you end up with 364 days a year. Each year your calendar drifts a day eventually after 7 years what was week1 does not match a like for like date of week1 the previous year.Eg2003 wk1 Mon 6th Jan 2003 -> Sun 12th Jan 20032004 wk1 Mon 5th Jan 2004 -> Sun 11th Jan 20042005 wk1 Mon 3rd Jan 2005 -> Sun 9th Jan 20052006 wk1 Mon 2nd Jan 2006 -> Sun 8th Jan 20062007 wk1 Mon 1st Jan 2007 -> Sun 7th Jan 20072008 wk1 Mon 31st Dec 2008 -> Sun 6th Jan 20082009 wk1 Mon 30th Dec 2009 -> Sun 5th Jan 2009To get things back in line, there in the concept of the 53 week year, in this case 2009 has 53 weeks, thus2010 wk1 Mon 4th Jan 2010 -> Sun 10th Jan 2010When week1 2010 is compared to week1 of 2009, one is not comparing like for like dates.This causes lots of complications in which we had to introduce a new 'compare to' type columns.I was wondering how other people have coped with this issue.

RE: 4-4-5 Calendar Functions, Part 1

corder — Sun, 05 Sep 2010 22:47:03 GMT

Good point about the date format. I've rarely dealt with dates outside the U.S. because my company only has local to me clients so I didn't even think about it. I'll definitely keep this in mind for the future.Cliff

RE: 4-4-5 Calendar Functions, Part 1

Ian Yates — Sun, 05 Sep 2010 21:37:52 GMT

Interesting concept. I'd probably use a date table only because I would need to expose the data in an SSAS cube and I already have a date dimension table there :)<rant>I only skimmed the article but the one glaring thing that would be wonderful to change is to use ISO date formats rather than US date formats. In Australia (and many other parts of the world), 7/1/10 reads as 7th January, which made for some confusing reading :) 2010-01-07 is universally understood. Nothing worse than when I am the local reseller for US-made software that doesn't play nice when the Windows date format doesn't exactly match mm/dd/yyyy.</rant>:-P

RE: 4-4-5 Calendar Functions, Part 1

Gagne — Wed, 13 Jan 2010 09:05:15 GMT

I read your articles and the discussion threads, that's what prompted me to post my code when I realized no one had posted a solution to build a 4-4-5 calendar when the first period doesn't really end on week #4 and the last period ends on a fixed date.

RE: 4-4-5 Calendar Functions, Part 1

corder — Wed, 13 Jan 2010 07:13:59 GMT

Thanks for the detailed implementation. They just published my Part 2 where I talk about getting the Period. http://www.sqlservercentral.com/articles/function/68323/. I mentioned in there that people do the counting in different ways and yours is one I haven't seen. Thanks again.Cliff

RE: 4-4-5 Calendar Functions, Part 1

Gagne — Mon, 11 Jan 2010 19:31:17 GMT

I know, old topic but better late than never.I had to build calendar tables a while ago. Recently I was force to revisit this because of a little trick the accounting department played on us. We use a 4-4-5 calendar but with certain specifics.1: Fiscal years always start on January 1st and end on December 31.2: Our periods end on Fridays3: For 2010 they've decided to push the first period up to January 29 instead of the 22nd because they didn't want the last period to be 6 weeks long.Because of this I had to rewrite the code that generates my calendar tables. Here's how I do it. I use 2 tables, FiscalCalendar to keep the periods for every day of the year ans FiscalPeriods to keep.......fiscal periods :-). To speed up any code that will use them I also created a few indexes.[code="sql"]Create Table FiscalCalendar( Period char(6), CalDate Datetime, PeriodYear SmallInt, PeriodMonth SmallInt)Create Index IX_FiscalCalendar_Date on FiscalCalendar (CalDate)Create Index IX_FiscalCalendar_Period on FiscalCalendar (Period)Create Table FiscalPeriods( PeriodMonth smallint, PeriodStartDate Datetime, PeriodEndDate Datetime, Period char(6))Create Index IX_FiscalPeriods_Period on FiscalPeriods (Period)Create Index IX_FIscalPeriods_Month on FiscalPeriods (PeriodMonth)Create Index IX_FiscalPeriods_StartDate on FiscalPeriods (PeriodStartDate)[/code]In the code that populates the tables I use a function that I wrote a long time ago. I'm sure it could be rewritten to be much nicer but it's simple and it served me well for a long time so there's no point in rewriting it .........if it's not broken............ZeroFill receives and integer and returns a string of the requested length padded with leading zeroes. It is very useful to convert year 2010 period 1 into 201001.[code="sql"]Create Function ZeroFill ( @myNum int, @myLength int)Returns char(10)AsBegin declare @StrValue char(20) declare @RetValue char(20) set @StrValue = cast(@myNum as char(20)) set @RetValue = replicate('0',@MyLength-len(@strValue)) + ltrim(rtrim(@StrValue)) return ltrim(rtrim(@Retvalue))end[/code]And here's the procedure itself. It takes the year and the day of the first month end as parameters.[code="sql"]Create Procedure CreateFiscalCalendarYear( @Year int, @Day int)AsDeclare @YearStartdate datetimeDeclare @YearEndDate DatetimeDeclare @FirstClosingDate DatetimeDeclare @NumDays intDeclare @Weeks01 intDeclare @Weeks02 intDeclare @Weeks03 intDeclare @Weeks04 intDeclare @Weeks05 intDeclare @Weeks06 intDeclare @Weeks07 intDeclare @Weeks08 intDeclare @Weeks09 intDeclare @Weeks10 intDeclare @Weeks11 intDeclare @Weeks12 int-- The 12 @Weeks variables keep the week numbers of each month end. Set @FirstClosingDate = cast(@Year as char(04)) + '-01-' + dbo.zerofill(@Day,2)Set @YearStartDate = cast(@Year -1 as char(4)) + '-12-31'Set @YearEndDate = cast(@Year as char(4)) + '-12-31' -- Note that I set my @YearStartDate variable to the last day of the previous year. -- That's because it's used to calculate the number of days of my year using DateX - DateY-- and I want it include January 1st. Not very nice but efficient and that calculation is-- necessary because we need to know if we're on a 365 days year or 366.-- Get the week of the first month end. This will be either 1 or 2.-- Then apply the remaining of a 4-4-5 "mask" to get the other week numbers.Set @Weeks01 = DatePart(Week, @FirstClosingDate)Set @Weeks02 = @Weeks01 + 4Set @Weeks03 = @Weeks02 + 5Set @Weeks04 = @Weeks03 + 4Set @Weeks05 = @Weeks04 + 4Set @Weeks06 = @Weeks05 + 5Set @Weeks07 = @Weeks06 + 4Set @Weeks08 = @Weeks07 + 4Set @Weeks09 = @Weeks08 + 5Set @Weeks10 = @Weeks09 + 4Set @Weeks11 = @Weeks10 + 4Set @Weeks12 = @Weeks11 + 5-- Get the number of days in the year. We will use it to create the-- right number of records in the FiscalCalendar table.Set @NumDays = (select DateDiff(day, @YearStartDate,@YearEndDate )) -- We all make mistakes and sometimes we have to re-run the code. Since our tables-- may already contain the information for the year we're working with we need to clean-- it upDelete from FiscalCalendar where year(CalDate) = @YearDelete from FiscalPeriods where Year(PeriodStartDate) = @Year-- and then create our data starting with the FiscalCalendar table. -- Using a Tally table (Thanks Jeff ;)) we can easily create our @Numdays records.Insert Into FiscalCalendar( CalDate, PeriodYear)Select top (@NumDays) DateAdd(Day, N, @YearStartDate), year(DateAdd(Day, N, @YearStartDate))From Tally1000 order by N-- Next I update the period month for the days that mark the end of a period.-- Using dense_rank and my @Weeks variable I can create a memory table-- that will hold my 12 end dates.-- Note that "where datepart(weekday,caldate) = 6 specifies my periods-- end on Fridays, which is day 6 of the week.Declare @myTable Table( Mydate datetime, pMonth int)insert into @myTableSelect CalDate, dense_rank() over(partition by year(CalDate) order by CalDate) from fiscalcalendar where datepart(weekday, CalDate) = 6 and datepart(week,CalDate) in ( @Weeks01, @Weeks02, @Weeks03, @Weeks04, @Weeks05, @Weeks06, @Weeks07, @Weeks08, @Weeks09, @Weeks10, @Weeks11, @Weeks12 )-- And then write them back to the FiscalCalendar table. The other columns will be-- populated later.Update FiscalCalendar set PeriodMonth = a.pMonth from @myTable a where myDate = fiscalcalendar.CalDate-- Time to get started with the FiscalPeriods table.-- We start by creating our 12 records. This is pretty easy since-- we happen to have 12 records in the FiscalCalendar table that-- have their period end dates.insert into FiscalPeriods ( PeriodEndDate)Select CalDate From fiscalcalendar where PeriodMonth is not null and year(caldate) = @Year order by Caldate-- Then we need to update the PeriodStartDate and PeriodEndDate columns.-- Two of them are easy. We know that the start date of the first period is January 1-- and that the end date of the last period is December 31.Update FiscalPeriods set PeriodStartDate = cast(year(PeriodEndDate) as char) + '-01-01'where month(PeriodEndDate) = 1 and Year(PeriodEndDate) = @YearUpdate FiscalPeriods set PeriodEndDate = cast(year(PeriodEndDate) as char) + '-12-31'where month(PeriodEndDate) = 12 and Year(PeriodEndDate) = @Year-- With those values in the table we can use a sub-query to update each PeriodStartDate-- with the previous PeriodEndDate + 1Update FiscalPeriods set PeriodStartDate = ( select dateadd(day, 1, max(PeriodEndDate)) from FiscalPeriods a where a.PeriodEndDate < fiscalPeriods.PeriodEndDate)where PeriodStartDate is null and Year(PeriodEndDate) = @Year-- The FiscalPeriods table require one last update to populate-- the Period column. Once again I'm using a memory table-- and dense_rankdeclare @MyTable2 Table( Period Char(4), PeriodStartDate datetime, PeriodEndDate datetime)Insert into @MyTable2 select dense_rank() over(partition by year(PeriodStartDate) order by PeriodEndDate), PeriodStartDate, PeriodEndDate From FiscalPeriods where Year(PeriodStartDate) = @Year-- With my memory table loaded I only need to delete records for that year-- in my physical table and reload it.Delete from FiscalPeriods where Year(PeriodStartDate) = @YearInsert into FiscalPeriods ( PeriodMonth, PeriodStartDate, PeriodEndDate, Period)Select Period, PeriodStartDate, PeriodEndDate, cast(year(periodstartdate) as char(4))+ dbo.zerofill(period,2)from @MyTable2-- With my FiscalPeriods table complete, I can come back to the FiscalCalendar-- table and update the empty columns that I didn't handle before from-- the FiscalPeriods table-- Period MonthsUpdate FiscalCalendar set PeriodMonth = a.PeriodMonthFrom FiscalPeriods a where PeriodYear = @Year and caldate between a.PeriodStartDate and a.PeriodEndDate-- Period CodesUpdate FiscalCalendar set Period = cast(PeriodYear as char(4)) + dbo.zerofill(PeriodMonth,2)where PeriodYear = @Year-- And create the procedurego[/code]Now I can add a year to my fiscal calendar with a simple procedure call passing it the year and the day of the first month end.Exec CreateFiscalCalendarYear 2010, 29My FiscalCalendar table contains an auto-increment integer column defined as Primary Key which I didn't put here. It's used to build relations with other tables to create fiscal calendar dimensions in my cubes.I also have a number of Fiscal Calendar oriented functions that are used in other processes.GetShortDate(): Very useful when trying to link a datetime field that contains the time part with the calendar.[code="sql"]Create Function GetShortDate( @vDate datetime)returns datetimeasbegin declare @wDate datetime Set @wDate = CAST(FLOOR(CAST(@vDate AS FLOAT ))AS DATETIME) Return @wDateEnd[/code]Getting the start date of the fiscal period for a given date.[code="sql"]Create Function GetFiscalBOM(@Date Datetime) returns Datetime asBeginDeclare @RetValue datetime Select @RetValue = PeriodStartDate from FiscalPeriods where @Date between PeriodStartDate and PeriodEndDate Return @RetValueend[/code]Getting the Period Code (YYYYMM) for a given date[code="sql"]Create Function GetFiscalPeriod(@Date datetime) returns Char(6)asBegin Declare @RetValue char(6) select @RetValue = period from FiscalCalendar where CalDate = dbo.getshortdate(@Date) Return @RetValueEnd[/code]Getting the start date of a given period[code="sql"]Create Function GetPeriodStartdate(@Period char(6))returns datetimeasBegin Declare @RetValue Datetime Select @RetValue = PeriodStartDate From FiscalPeriods where Period = @Period Return @RetValueEnd[/code]I'm sure some will criticize my code and come up with much nicer ways of doing this but so far I haven't seen any code on the forum to build a 4-4-5 calendar starting on January 1st, ending on December 31 and with a first month end date different than where 4 weeks puts you.Hopefully it can help some people. At least it will show Jeff one more coder who actually did it......I even use the Tally table technique that I learned from your article :)

RE: 4-4-5 Calendar Functions, Part 1

andyscott — Wed, 30 Sep 2009 03:26:50 GMT

[quote][b]corder (9/29/2009)[/b][hrI have no idea how common it is but we had been running our application for about 10 years before it came up so we had to come up with a solution. And I think, non-accountant that I am, that every few years there is an extra week thrown in. I mention this in my article and andyscott mentions it, too.A further question I have on a table implementation (yes, I think it's a great idea but I'm pondering things) is what about shifting the start date of the year. Meaning, if you build the table where year 2008 starts on 1/6/2008 then the table's data is fine. But what happens if the accountants decide that the year should start on the first Sunday after Sept 1st? Then another accountant says it should start on the first Tuesday after July 1st? I'm thinking you'd need more key values in the table but I'm curious if anyone has any great implementation ideas.[/quote]I think it depends on the nature of your company's business - if business is very geared to a weekly cycle, then the imposition of the "calendar month" as a "period" is too inflexible. Hence - if your business still wishes to report on a pseudo-monthly cycle, ie twelve times in a year - the need for fiscal periods each of an integral number of weeks. But the definition of the year itself - in terms of when it starts and ends and, in some instances, what any interim points are - is (at least for a UK-based, publically listed company) strictly controlled, since it is a requirement of its registration on the stock market. So its not just down to the accountants to choose! Irrespective of whether you 4-4-5 or any other method of weekly-based reporting, there will be a requirement to insert a 53rd week from time to time. This comes about because of (a) 52 weeks of 7 days is one day short of a normal year of 365 days and (b) leap years add a further day every (nearly) four years too. So after 6 years, you'll have had an extra 6 days from (a) and, likely, an extra 1 day from (b), and you'll need to cater for a 53rd week. Depending on timing (ie where the leap years are - or aren't!) this may come after the fifth year or may be delayed until the seventh - but every sixth year is what you should plan for. If your business still wants to report over 12 periods, then one of the existing periods will need to have the extra week added.But even if you do have to have two (or more) sets of cycles (for instance, if you are doing reporting for business with different year start/end dates) its easy to extend a calendar table to contain multiple sets of fiscal period data by having distinct sets of columns for each set of cycles.

RE: 4-4-5 Calendar Functions, Part 1

corder — Tue, 29 Sep 2009 11:37:30 GMT

[quote][b]drnetwork (9/29/2009)[/b][hr]How common is this 4-4-5 (or variant) thinking in modern accounting systems? This is the first I've heard of it and I've DBA'ed behind three different fairly popular accounting systems, none of which incorporated this possibility. The assumption was always that a period was monthly.Then my second question is that every 5 or 6 years you'll need an extra week thrown in somewhere. Do the accountants just make a 4-5-5 in a fiscal quarter?[/quote]I have no idea how common it is but we had been running our application for about 10 years before it came up so we had to come up with a solution. And I think, non-accountant that I am, that every few years there is an extra week thrown in. I mention this in my article and andyscott mentions it, too.A further question I have on a table implementation (yes, I think it's a great idea but I'm pondering things) is what about shifting the start date of the year. Meaning, if you build the table where year 2008 starts on 1/6/2008 then the table's data is fine. But what happens if the accountants decide that the year should start on the first Sunday after Sept 1st? Then another accountant says it should start on the first Tuesday after July 1st? I'm thinking you'd need more key values in the table but I'm curious if anyone has any great implementation ideas.Cliff

RE: 4-4-5 Calendar Functions, Part 1

drnetwork — Tue, 29 Sep 2009 10:38:55 GMT

How common is this 4-4-5 (or variant) thinking in modern accounting systems? This is the first I've heard of it and I've DBA'ed behind three different fairly popular accounting systems, none of which incorporated this possibility. The assumption was always that a period was monthly.Then my second question is that every 5 or 6 years you'll need an extra week thrown in somewhere. Do the accountants just make a 4-5-5 in a fiscal quarter?

RE: 4-4-5 Calendar Functions, Part 1

Jeff Moden — Tue, 29 Sep 2009 10:04:17 GMT

[quote][b]andyscott (9/29/2009)[/b][hr]Here's the approach that I use - with a bit of code - and that has served me well in a number of UK-based organisations. [/quote]Very cool. That's a heck of an explanation. Thanks for taking the time.

RE: 4-4-5 Calendar Functions, Part 1

Jeff Moden — Tue, 29 Sep 2009 10:02:32 GMT

[quote][b]Ryan C. Price (9/29/2009)[/b][hr][quote][b]Jeff Moden (9/28/2009)[/b][hr]Heh... I see 3 recommendations to build a Calendar table and two that claim they've used one, yet no one has offered up any of their own code to show they have ever done so. ;-) Like the tag line from the movie goes... Show Me the Money! :-P[/quote]Jeff - did you count my rambling on about week numbers as one of those recommendations ?if so, you're making me feel all guilty and I'll have to go off and try and find a copy of that database in an archive somewhere...../Ryan[/quote]Heh... I guess my comment worked. Good folks are coughing up some good stuff. Thank you one and all.

RE: 4-4-5 Calendar Functions, Part 1

corder — Tue, 29 Sep 2009 09:17:57 GMT

I've seen several replies about using a table to store all of the data that I might need. I have explored this as an option and I definitely think it is a possibility. As Joe Celko pointed out my approach is not SET based and SQL server does like its sets. I try to use them when I can and if you can implement this idea with sets I'd say that's a great way to go. I'll probably even do a table implementation myself. One thing missing from the table suggestions is the idea that the starting day of the week could change. The starting day could be Sunday for one company and Monday for another company. You could still build a table to take this into account. Let me know if I'm missing something and the tables do take this into account.The comment from Ryan Price is also interesting. I'd like to see some performance metrics on date range joins. In the end, I came up with a little function that worked for me and wanted to share it since I had a lot of trouble finding much out there. I'm going to share my function for getting a Period (like a month), which is also procedural based. For a table implementation maybe my functions could be modified to populate a table. Sounds like I or someone could even do a comparison article on function based, set based, and various join methods. That could be fun.Thanks everyone for your comments. Keep them coming.:-)

RE: 4-4-5 Calendar Functions, Part 1

Ryan C. Price — Tue, 29 Sep 2009 07:30:07 GMT

[quote][b]Jeff Moden (9/28/2009)[/b][hr]Heh... I see 3 recommendations to build a Calendar table and two that claim they've used one, yet no one has offered up any of their own code to show they have ever done so. ;-) Like the tag line from the movie goes... Show Me the Money! :-P[/quote]Jeff - did you count my rambling on about week numbers as one of those recommendations ?if so, you're making me feel all guilty and I'll have to go off and try and find a copy of that database in an archive somewhere...../Ryan

RE: 4-4-5 Calendar Functions, Part 1

andyscott — Tue, 29 Sep 2009 03:20:59 GMT

Here's the approach that I use - with a bit of code - and that has served me well in a number of UK-based organisations. For background, UK fiscal years can start almost anywhere in the "real" calendar - my current organisation begins and ends theirs on the 4th Sunday in September (so the current year, just begun - 2009/10 - is a "53" week one). Quarter/Period patterns are generally 4-4-5, except that in a 53-week year, one of them will be either a 4-5-5 or a 5-4-5 one. The problem here is that the determination of which quarter is to get that treatment, and what the treatment is, is determined by the executives relatively close to the time so it is not practical to load the calander table too far ahead!The approach I use has a "seed" table with 371 (i.e. 53 weeks worth) or days in it. This has five columns: pkintSeedDay - day of the fiscal year (1 thru 371)intSeedPeriod - corresponding period for that day if a 52 week yearintSeedWeekOfPeriod - corresponding week of the period for that day if a 52 week yearintSeedPeriod53 - corresponding period for that day if a 53 week yearintSeedWeekOfPeriod53 - corresponding week of the period for that day if a 53 week yearYou can set this up manually or write a bit of code to do so - but its done once only.The main calendar table has eleven columns (you can remove any you don't want or add any that would be of value) as follows:pkdatDate - the actual dateintDayOfFY - the day within the fiscal year - 1 to 364 or 371intWeekOfPeriod - the week within the period - 1 to 4 or 5intPeriodOfFY - the relevent period - 1 to 12intQuarterofFY - the relevent quarter - 1 to 4intFYID - a value that can be used to get the textual name of the financial year from a lookup table (eg fiscal year "9" may lookup the text "2009/2010") for reportingintWeekOfFY - the week number within the fiscal year - 1 to 52 or 53intDayOfWeek - the day within the company week - 1 to 7 (see note below)intWeeksAgo - this and the next two columns are updated daily (see notes below) intDaysAgo -intPeriodsAgo -Note regarding intDayOfWeek: this is stored because use of DATEPART to get the same thing is langauage/locale dependent whereas the company has its own rigid interpretation of when a week starts and ends.Note regarding intDaysAgo: this is set to a zero if this row reflects todays date, with positive numbers representing days already past and negative numbers representing days in the future. Note regarding intWeeksAgo: this is set to a zero if this row is in the same fiscal week as todays date, with positive numbers representing weeks already past and negative numbers representing weeks in the future. Note regarding intPeriodsAgo: this is set to a zero if this row is in the same fiscal period as todays date, with positive numbers representing periods already past and negative numbers representing periods in the future.Each year, the following code is executed (after making any required changes) to load a new years worth of rows into the calandar table using the feeder table as a base. If it is a 53-week year (and the "extra" week has moved since the last time a 53-week year was loaded) then the feeder table will need to be realigned first using the code at the foot of this article.[code="sql"]USE EXTRACT;DECLARE @startdate datetime, @fy int, @fyname char(7), @days int---- Set the parameters below to the required values---- SET @startdate = '2007-09-30'SET @startdate = (SELECT MAX(pkdatDate)+1 FROM tblFinancialCalendar)-- Comment out one of the above two lines: -- use the first if you want to set a fixed start date and use the second if you want to append a new year to the end of the existing tableSET @fy = 9SET @fyname = 'FY09/10'SET @days = 371-- @days must be set to 364 for a 52 week year, or set to 371 for a 53 week year -- ( but check that the additional week is in the correct period in the -- CalendarFeeder table first or run prcCalendarFeeder53Setup to reset it )---- Load the new year into the main table from the feeder table--INSERT INTO tblFinancialCalendarSELECT @startdate-1+pkintSeedDay, -- pkdatDate pkintSeedDay, -- intDayOfFY CASE WHEN @days=364 THEN intSeedWeekOfPeriod ELSE intSeedWeekOfPeriod53 END, -- intWeeekOfPeriod CASE WHEN @days=364 THEN intSeedPeriod ELSE intSeedPeriod53 END, -- intPeriodOfFY CASE WHEN intSeedPeriod>= 1 and intSeedPeriod<=3 THEN 1 WHEN intSeedPeriod>= 4 and intSeedPeriod<=6 THEN 2 WHEN intSeedPeriod>= 7 and intSeedPeriod<=9 THEN 3 WHEN intSeedPeriod>= 10 and intSeedPeriod<=12 THEN 4 END, -- intQuarterOfFY @fy, -- intFYID FLOOR((pkintSeedDay-1)/7)+1, -- intWeekOfFY NULL, -- intDayOfWeek (set below) NULL, -- intWeeksAgo (reset daily) NULL, -- intDaysAgo (reset daily) NULL -- intPeriodsAgo (reset daily)FROM tblCalendarFeederWHERE pkintSeedDay<=@days;---- Update the calandar to set the "day of the week" values-- (These are used to prevent issues caused by the DATEPART(dw,xxx) function which gives variable outputs dependent on language -- and other settings that may be in effect at a per-user level)--SET DATEFIRST 7; -- Our financial weeks run from Sunday to SaturdayUPDATE tblFinancialCalendar SET intDayOfWeek=DATEPART(dw,pkdatDate);---- Add or update new year into the Financial Year Names table--DELETE FROM tblCalendarFinancialYearNames WHERE pkintFYID=@fy;INSERT INTO tblCalendarFinancialYearNamesSELECT@fy,@fyname; [/code]The table can now be joined to any date elsewhere in the database to obtain fiscal period data.Each night the "DaysAgo", "WeeksAgo" and "PeriodsAgo" columns are recalculated using the following code:.[code="sql"]UPDATE tblFinancialCalendar SET intWeeksAgo=DATEDIFF(wk,pkdatDate,GETDATE());UPDATE tblFinancialCalendar SET intDaysAgo=DATEDIFF(dy,pkdatDate,GETDATE());UPDATE tblFinancialCalendar SET intPeriodsAgo=(SELECT COUNT(DISTINCT intFYID*100+intPeriodOfFY)-1 FROM tblFinancialCalendar b WHERE pkdatDate BETWEEN tblFinancialCalendar.pkdatDate AND GETDATE())WHERE tblFinancialCalendar.pkdatDate<=GETDATE();UPDATE tblFinancialCalendar SET intPeriodsAgo=(SELECT -COUNT(DISTINCT intFYID*100+intPeriodOfFY)+1 FROM tblFinancialCalendar b WHERE pkdatDate BETWEEN GETDATE() AND tblFinancialCalendar.pkdatDate)WHERE tblFinancialCalendar.pkdatDate>GETDATE();[/code]These columns can then be used to select data from tables to which the calandar has been joined, for reporting perposes. For example, you could select all data from the previous period by adding a "WHERE intPeriodsAgo=1" clause.Finally, here is the code that I use to reset the 53rd week if it has moved since the last time a 53-week year was added:[code="sql"]DECLARE @period int,@cut int;---- Set the following parameter before running--SET @period=11;-- Enter the period which is to have the extra 5th week (should be one of 1,2,4,5,7,8,10 or 11)SET @cut=(SELECT MAX(pkintSeedDay) FROM tblCalendarFeeder WHERE intSeedPeriod=@period);CREATE TABLE tblCalendarFeeder2 (pkintSeedDay INT NOT NULL PRIMARY KEY,intSeedPeriod INT NOT NULL,intSeedWeekOfPeriod INT NOT NULL);UPDATE tblCalendarFeeder SET intSeedPeriod53=NULL,intSeedWeekOfPeriod53=NULL;INSERT INTO tblCalendarFeeder2 SELECT pkintSeedDay,intSeedPeriod,intSeedWeekOfPeriod FROM tblCalendarFeeder; UPDATE tblCalendarFeeder SET intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut), intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut)WHERE tblCalendarFeeder.pkintSeedDay<=@cut;UPDATE tblCalendarFeeder SET intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7), intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7)WHERE tblCalendarFeeder.pkintSeedDay>@cut+7;UPDATE tblCalendarFeeder SET intSeedPeriod53=@period,intSeedWeekOfPeriod53=5 WHERE intSeedPeriod53 IS NULL;DROP TABLE tblCalendarFeeder2;[/code]

RE: 4-4-5 Calendar Functions, Part 1

Jeff Moden — Mon, 28 Sep 2009 23:51:49 GMT

[quote][b]wldhrs (9/28/2009)[/b][hr]thomasrawley, while i detest your naming conventions, how many compilers care if the name is fscl_yr, how many compilers care if the name is [Fiscal Year] ... go on bite me Jeff, [/quote]Heh... bite yourself... I don't care what you call your stuff. ;-) I'd much rather just see FiscalYear instead of the abbreviations or the report formatted column name, but whatever.[quote]Jeff, are you actually against calendar tables or are you just playing the game? [/quote]Oh heck no... not against calendar tables at all. I just see all those folks talking about them but no code to help others out.[quote]Editted to fix missing ]'s, Doh.[/quote]Heh... see? ;-)

RE: 4-4-5 Calendar Functions, Part 1

wldhrs — Mon, 28 Sep 2009 22:53:43 GMT

thomasrawley, while i detest your naming conventions, how many compilers care if the name is fscl_yr, how many compilers care if the name is [Fiscal Year] ... go on bite me Jeff, the join table is excellent.joe.rojas, the CFO, ultimately, signs the cheques, very good call. Joe Celko, coooool, I like it when temporal gets used in context. Like, in the context of, "we do [pick one] [DP, IM, IT, ITC, [iterate vowel year plus RAND()-3, consonant year plus RAND()+2)], etc] Jeff, are you actually against calendar tables or are you just playing the game? I've tried to build calendar functions that allow for the various changes that Popes required to be implemented for the Gregorian calendar in order to allow for various mid-European country changes that were out of the Papally-prescribed sequence, which is a very cool but in the majority of cases outside of university research, a complete waste of time, ... and the join table is really easy as a solution.Maybe an MS maintained CLR function could be better, but I'm way too far from MS Central to comment on that idea.Editted to fix missing ]'s, Doh.

RE: 4-4-5 Calendar Functions, Part 1

brewmanz — Mon, 28 Sep 2009 22:06:50 GMT

Not SQL-related, but in Excel I created a staff incentive scheme for a company using 4-4-5 ,and then had to 'adjust' every 3 or 4 years to bring the weeks into line. We then decided to switch to a '60 week year' where each month has either 4 or 5 weeks, with the Thursday deciding which month the split-week falls in. All monthly reports have 5 weekly columns. Sales targets for a 4 week month last year that's 5 weeks this year are 'stretched' to be pro-rata-ed and vice-versa. Week Number is an internal concept and runs from 1 to 59 or 60, with 'missing weeks'. it's easy to convert Week Number to Month as each month starts 5 weeks after the previous one. It automatically adjusts to 4-5-4 or 5-4-4, and occasionally 5-4-5. No, I've no code available for you, but giving you food for thought about how to handle 4-4-5 type concept without the '364 days per year' limitation.RegardsBrewmanz

RE: 4-4-5 Calendar Functions, Part 1

Jeff Moden — Mon, 28 Sep 2009 20:05:27 GMT

Heh... I see 3 recommendations to build a Calendar table and two that claim they've used one, yet no one has offered up any of their own code to show they have ever done so. ;-) Like the tag line from the movie goes... Show Me the Money! :-P

RE: 4-4-5 Calendar Functions, Part 1

CELKO — Mon, 28 Sep 2009 18:52:19 GMT

Your approach is fundamentally wrong. Get a copy of THINKING IN SETS for the details. This kind of temporal work is best done with a Calendar table (set-oriented programming and tables) and not with HIGHLY proprietary procedural code (1950's COBOL and file systems).

RE: 4-4-5 Calendar Functions, Part 1

joe.rojas — Mon, 28 Sep 2009 18:15:20 GMT

I had a great set of functions that determined fiscal period and year until one year had both 4-4-5 AND a 4-5-5.After that, I stored the periods in a table that contained the start and end of the periods and then gave Finance access to update the table.Problem solved. :-D

RE: 4-4-5 Calendar Functions, Part 1

Slope — Mon, 28 Sep 2009 17:04:51 GMT

Great article, Cliff! I would recommend building a date dimension table for this kind of thing. Add columns for everything you might want to know about each date. For example:CalendarDateFiscalYearFiscalQuarterNumberFiscalQuarterStartDateFiscalQuarterEndDateFiscalPeriodNumberFiscalPeriodStartDateFiscalPeriodEndDateWeeksInFiscalPeriodDaysInFiscalPeriodDayNumberOfFiscalPeriod<blah><blah>Use your functions to populate the table with 20 years worth of dates (or however much you need for your business model) so that all of that information is sitting there ready to go. Once you have your table setup, life becomes very simple. You no longer have a need to write complicated (and sometimes slow running) expressions. Instead, you can get whatever you need using simple SELECTs.

RE: 4-4-5 Calendar Functions, Part 1

Ryan C. Price — Mon, 28 Sep 2009 17:03:21 GMT

probably slightly off topic, but this reminds me of a job I did some years ago where the client was only interested in years and weeks - a fresh produce packing facility where I was doing work on the program for recording product 'production' (packing tomatoes into crates, mostly).They did 'invoicing' runs on a Monday, and other housekeeping on other days of the week. Basically, they worked with 'ISO' weeks, but wanted to be able to 'adjust' their weeks sliding the start/end days to manage their shifts, in particular around holiday season (Christmas/New Year in New Zealand, just about everything shuts down), and so we ended up creating a 'WeekNumber' table with Start/End dates and a bunch of queries that joined to this table on a time stamp (that's a datetime, not a rowversion) field.It was great while the database was really small, but after a few million crates the performance of the queries started to drop off dramatically, and I discovered how bad a StartDate <= RecordDate <= EndDate join performs!.Now our WeekNumber table had an integer primary key that took the form YYYYww (is that a natural key ?) - we chose to 'denormalise' and add a 'WeekNumber' column to all those tables with a time stamp, and using a trigger (didn't want to confuse the VB6 front-end at all), populated the field based on the time stamp on each insert/update - the integer equi-join being way faster than the old one./Ryan

RE: 4-4-5 Calendar Functions, Part 1

thomasrawley — Mon, 28 Sep 2009 12:24:58 GMT

I prefer the method of using a table to store fiscal period information.[code]CREATE TABLE [dbo].[fscl_yr_wk]( [fscl_yr] [smallint] NULL, [fscl_yr_wk_nbr] [smallint] NULL, [fscl_prd_nbr] [smallint] NULL, [wk_bgn_dt] [datetime] NULL, [wk_end_dt] [datetime] NULL, [fscl_mth_wk_nbr] [smallint] NULL, [fscl_yr_wk_key_val] [nvarchar](6) NULL, [fscl_prd_key_val] [nvarchar](6) NULL)[/code]In the data model for this table, fscl_prd_nbr is the fiscal month. With these columns you can derive pretty much any fiscal date information you need. I have created functions that take parameters of fiscal month and/or fiscal year (or use getdate() for functions that return "current" fiscal information).

RE: 4-4-5 Calendar Functions, Part 1

corder — Mon, 28 Sep 2009 07:08:39 GMT

Glad I could help. Cliff

RE: 4-4-5 Calendar Functions, Part 1

Carolyn Richardson — Mon, 28 Sep 2009 05:12:51 GMT

Very timely I was looking for something very similar to this, thanks.

4-4-5 Calendar Functions, Part 1

corder — Sat, 26 Sep 2009 12:00:14 GMT

Comments posted to this topic are about the item [B]4-4-5 Calendar Functions, Part 1[/B]