SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


4-4-5 Calendar Functions, Part 1


4-4-5 Calendar Functions, Part 1

Author
Message
Gagne
Gagne
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 361
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 Fridays
3: 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.


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)



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.

Create Function ZeroFill 
(
@myNum int,
@myLength int
)
Returns char(10)
As
Begin
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



And here's the procedure itself. It takes the year and the day of the first month end as parameters.



Create Procedure CreateFiscalCalendarYear
(
@Year int,
@Day int
)
As

Declare @YearStartdate datetime
Declare @YearEndDate Datetime
Declare @FirstClosingDate Datetime
Declare @NumDays int
Declare @Weeks01 int
Declare @Weeks02 int
Declare @Weeks03 int
Declare @Weeks04 int
Declare @Weeks05 int
Declare @Weeks06 int
Declare @Weeks07 int
Declare @Weeks08 int
Declare @Weeks09 int
Declare @Weeks10 int
Declare @Weeks11 int
Declare @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 + 4
Set @Weeks03 = @Weeks02 + 5

Set @Weeks04 = @Weeks03 + 4
Set @Weeks05 = @Weeks04 + 4
Set @Weeks06 = @Weeks05 + 5

Set @Weeks07 = @Weeks06 + 4
Set @Weeks08 = @Weeks07 + 4
Set @Weeks09 = @Weeks08 + 5

Set @Weeks10 = @Weeks09 + 4
Set @Weeks11 = @Weeks10 + 4
Set @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 up

Delete from FiscalCalendar where year(CalDate) = @Year
Delete from FiscalPeriods where Year(PeriodStartDate) = @Year


-- and then create our data starting with the FiscalCalendar table.
-- Using a Tally table (Thanks Jeff Wink) 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 @myTable
Select 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) = @Year

Update 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 + 1

Update 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_rank

declare @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) = @Year

Insert 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 Months
Update FiscalCalendar
set PeriodMonth = a.PeriodMonth
From FiscalPeriods a
where PeriodYear = @Year and caldate between a.PeriodStartDate and
a.PeriodEndDate

-- Period Codes
Update FiscalCalendar
set Period = cast(PeriodYear as char(4)) + dbo.zerofill(PeriodMonth,2)
where PeriodYear = @Year

-- And create the procedure
go



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, 29


My 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.

Create Function GetShortDate
(
@vDate datetime
)
returns datetime
as
begin
declare @wDate datetime
Set @wDate = CAST(FLOOR(CAST(@vDate AS FLOAT ))AS DATETIME)
Return @wDate
End



Getting the start date of the fiscal period for a given date.

Create Function GetFiscalBOM(@Date Datetime) returns Datetime
as
Begin
Declare @RetValue datetime
Select @RetValue = PeriodStartDate
from FiscalPeriods
where @Date between PeriodStartDate and PeriodEndDate
Return @RetValue
end



Getting the Period Code (YYYYMM) for a given date

Create Function GetFiscalPeriod(@Date datetime) returns Char(6)
as
Begin
Declare @RetValue char(6)
select @RetValue = period
from FiscalCalendar
where CalDate = dbo.getshortdate(@Date)
Return @RetValue
End




Getting the start date of a given period

Create Function GetPeriodStartdate(@Period char(6))
returns datetime
as
Begin
Declare @RetValue Datetime
Select @RetValue = PeriodStartDate From FiscalPeriods
where Period = @Period
Return @RetValue
End



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 Smile
corder
corder
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 298
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



Gagne
Gagne
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 361
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.
Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1794 Visits: 445
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 Smile

<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 Smile 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



corder
corder
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 298
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



ian.fickling
ian.fickling
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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.

Eg
2003 wk1 Mon 6th Jan 2003 -> Sun 12th Jan 2003
2004 wk1 Mon 5th Jan 2004 -> Sun 11th Jan 2004
2005 wk1 Mon 3rd Jan 2005 -> Sun 9th Jan 2005
2006 wk1 Mon 2nd Jan 2006 -> Sun 8th Jan 2006
2007 wk1 Mon 1st Jan 2007 -> Sun 7th Jan 2007
2008 wk1 Mon 31st Dec 2008 -> Sun 6th Jan 2008
2009 wk1 Mon 30th Dec 2009 -> Sun 5th Jan 2009

To get things back in line, there in the concept of the 53 week year, in this case 2009 has 53 weeks, thus
2010 wk1 Mon 4th Jan 2010 -> Sun 10th Jan 2010

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.
brewmanz
brewmanz
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 406
ian.fickling (9/6/2010)

...
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.

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 helps

Brewmanz
Athos Athanasiou
Athos Athanasiou
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
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.
ian.fickling
ian.fickling
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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
brewmanz
brewmanz
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 406
ian.fickling (9/6/2010)

Still not clear to me how you have resolved the week on week comparison.

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 ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search