Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

4-4-5 Calendar Functions, Part 1 Expand / Collapse
Author
Message
Posted Wednesday, January 13, 2010 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:40 AM
Points: 39, Visits: 296
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



Post #846864
Posted Wednesday, January 13, 2010 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:43 AM
Points: 102, Visits: 349
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.
Post #846979
Posted Sunday, September 5, 2010 9:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 14, 2015 8:59 PM
Points: 1,038, 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 :)

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



Post #980894
Posted Sunday, September 5, 2010 10:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:40 AM
Points: 39, Visits: 296
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



Post #980900
Posted Monday, September 6, 2010 1:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 6, 2010 7:58 AM
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.
Post #980918
Posted Monday, September 6, 2010 1:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 22, 2014 8:09 PM
Points: 489, 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
Post #980925
Posted Monday, September 6, 2010 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 6, 2010 11:26 AM
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.
Post #980998
Posted Monday, September 6, 2010 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 6, 2010 7:58 AM
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

Post #981018
Posted Monday, September 6, 2010 5:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 22, 2014 8:09 PM
Points: 489, 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 ...
Post #981026
Posted Monday, September 6, 2010 7:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:40 AM
Points: 39, Visits: 296
There sure are a lot of variations on this idea. The accountants are even trickery than I thought. :)


Post #981118
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse