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 Saturday, September 26, 2009 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 12:27 PM
Points: 39, Visits: 295
Comments posted to this topic are about the item 4-4-5 Calendar Functions, Part 1


Post #794182
Posted Monday, September 28, 2009 5:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 2:12 AM
Points: 1,451, Visits: 2,918
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
Post #794434
Posted Monday, September 28, 2009 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 12:27 PM
Points: 39, Visits: 295
Glad I could help.

Cliff



Post #794485
Posted Monday, September 28, 2009 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 7:44 AM
Points: 1, Visits: 72
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).
Post #794678
Posted Monday, September 28, 2009 5:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:13 PM
Points: 56, Visits: 1,028
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
Post #794829
Posted Monday, September 28, 2009 5:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 10:55 AM
Points: 8, Visits: 139
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.

Post #794831
Posted Monday, September 28, 2009 6:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 18, 2009 6:26 PM
Points: 1, 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.
Post #794853
Posted Monday, September 28, 2009 6:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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).

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #794863
Posted Monday, September 28, 2009 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
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!

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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #794879
Posted Monday, September 28, 2009 10:06 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:05 AM
Points: 488, Visits: 400
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
Post #794899
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse