4-4-5 Calendar Functions, Part 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cliff Corder » 4-4-5 Calendar Functions, Part 1

38 posts, Page 1 of 4

1 2 3 4 »»»

4-4-5 Calendar Functions, Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

corder

Posted Saturday, September 26, 2009 12:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:17 PM
Points: 36, Visits: 268

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

Post #794182

Carolyn Richardson

Posted Monday, September 28, 2009 5:12 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:52 AM
Points: 1,397, Visits: 2,738

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

corder

Posted Monday, September 28, 2009 7:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:17 PM
Points: 36, Visits: 268

Glad I could help.

Cliff

Post #794485

thomasrawley

Posted Monday, September 28, 2009 12:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:59 AM
Points: 1, Visits: 64

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

Ryan C. Price

Posted Monday, September 28, 2009 5:03 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:38 AM
Points: 54, Visits: 913

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

Slope

Posted Monday, September 28, 2009 5:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 08, 2013 5:27 PM
Points: 8, Visits: 135

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

joe.rojas

Posted Monday, September 28, 2009 6:15 PM

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

Post #794853

CELKO

Posted Monday, September 28, 2009 6:52 PM

SSCommitted

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

Jeff Moden

Posted Monday, September 28, 2009 8:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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

Like the tag line from the movie goes... Show Me the Money! Tongue

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #794879

brewmanz

Posted Monday, September 28, 2009 10:06 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:16 AM
Points: 477, Visits: 389

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 »

38 posts, Page 1 of 4

1 2 3 4 »»»

Permissions