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
corder
corder
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 298
Comments posted to this topic are about the item 4-4-5 Calendar Functions, Part 1



Carolyn Richardson
Carolyn Richardson
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3337 Visits: 3552
Very timely I was looking for something very similar to this, thanks.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
corder
corder
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 298
Glad I could help.

Cliff



thomas@rawley.net
thomas@rawley.net
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 85
I prefer the method of using a table to store fiscal period information.


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
)



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).
Ryan C. Price
Ryan C. Price
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 1229
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
Slope
Slope
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 143
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:

CalendarDate
FiscalYear
FiscalQuarterNumber
FiscalQuarterStartDate
FiscalQuarterEndDate
FiscalPeriodNumber
FiscalPeriodStartDate
FiscalPeriodEndDate
WeeksInFiscalPeriod
DaysInFiscalPeriod
DayNumberOfFiscalPeriod
<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.
joe.rojas
joe.rojas
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 3
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)

Group: General Forum Members
Points: 280210 Visits: 42249
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

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
brewmanz
brewmanz
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 406
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.
Regards
Brewmanz
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