4-4-5 Calendar Functions, Part 1

  • corder

    Ten Centuries

    Points: 1275

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

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8300

    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[/url]

  • corder

    Ten Centuries

    Points: 1275

    Glad I could help.

    Cliff

  • thomas@rawley.net

    Valued Member

    Points: 65

    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

    Ten Centuries

    Points: 1150

    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

    Old Hand

    Points: 352

    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

    Valued Member

    Points: 71

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

  • Jeff Moden

    SSC Guru

    Points: 995177

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • brewmanz

    SSCommitted

    Points: 1574

    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

  • wldhrs

    SSC Eights!

    Points: 911

    thomasrawley, while i detest your naming conventions, how many compilers care if the name is fscl_yr, how many compilers care if the name is [Fiscal Year] ... go on bite me Jeff,

    the join table is excellent.

    joe.rojas, the CFO, ultimately, signs the cheques, very good call.

    Joe Celko, coooool, I like it when temporal gets used in context. Like, in the context of, "we do [pick one] [DP, IM, IT, ITC, [iterate vowel year plus RAND()-3, consonant year plus RAND()+2)], etc]

    Jeff, are you actually against calendar tables or are you just playing the game? I've tried to build calendar functions that allow for the various changes that Popes required to be implemented for the Gregorian calendar in order to allow for various mid-European country changes that were out of the Papally-prescribed sequence, which is a very cool but in the majority of cases outside of university research, a complete waste of time, ... and the join table is really easy as a solution.

    Maybe an MS maintained CLR function could be better, but I'm way too far from MS Central to comment on that idea.

    Editted to fix missing ]'s, Doh.

    Peter Edmunds ex-Geek

  • Jeff Moden

    SSC Guru

    Points: 995177

    wldhrs (9/28/2009)


    thomasrawley, while i detest your naming conventions, how many compilers care if the name is fscl_yr, how many compilers care if the name is [Fiscal Year] ... go on bite me Jeff,

    Heh... bite yourself... I don't care what you call your stuff. 😉 I'd much rather just see FiscalYear instead of the abbreviations or the report formatted column name, but whatever.

    Jeff, are you actually against calendar tables or are you just playing the game?

    Oh heck no... not against calendar tables at all. I just see all those folks talking about them but no code to help others out.

    Editted to fix missing ]'s, Doh.

    Heh... see? 😉

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • andyscott

    Ten Centuries

    Points: 1154

    Here's the approach that I use - with a bit of code - and that has served me well in a number of UK-based organisations.

    For background, UK fiscal years can start almost anywhere in the "real" calendar - my current organisation begins and ends theirs on the 4th Sunday in September (so the current year, just begun - 2009/10 - is a "53" week one). Quarter/Period patterns are generally 4-4-5, except that in a 53-week year, one of them will be either a 4-5-5 or a 5-4-5 one. The problem here is that the determination of which quarter is to get that treatment, and what the treatment is, is determined by the executives relatively close to the time so it is not practical to load the calander table too far ahead!

    The approach I use has a "seed" table with 371 (i.e. 53 weeks worth) or days in it. This has five columns:

    pkintSeedDay - day of the fiscal year (1 thru 371)

    intSeedPeriod - corresponding period for that day if a 52 week year

    intSeedWeekOfPeriod - corresponding week of the period for that day if a 52 week year

    intSeedPeriod53 - corresponding period for that day if a 53 week year

    intSeedWeekOfPeriod53 - corresponding week of the period for that day if a 53 week year

    You can set this up manually or write a bit of code to do so - but its done once only.

    The main calendar table has eleven columns (you can remove any you don't want or add any that would be of value) as follows:

    pkdatDate - the actual date

    intDayOfFY - the day within the fiscal year - 1 to 364 or 371

    intWeekOfPeriod - the week within the period - 1 to 4 or 5

    intPeriodOfFY - the relevent period - 1 to 12

    intQuarterofFY - the relevent quarter - 1 to 4

    intFYID - a value that can be used to get the textual name of the financial year from a lookup table (eg fiscal year "9" may lookup the text "2009/2010") for reporting

    intWeekOfFY - the week number within the fiscal year - 1 to 52 or 53

    intDayOfWeek - the day within the company week - 1 to 7 (see note below)

    intWeeksAgo - this and the next two columns are updated daily (see notes below)

    intDaysAgo -

    intPeriodsAgo -

    Note regarding intDayOfWeek: this is stored because use of DATEPART to get the same thing is langauage/locale dependent whereas the company has its own rigid interpretation of when a week starts and ends.

    Note regarding intDaysAgo: this is set to a zero if this row reflects todays date, with positive numbers representing days already past and negative numbers representing days in the future.

    Note regarding intWeeksAgo: this is set to a zero if this row is in the same fiscal week as todays date, with positive numbers representing weeks already past and negative numbers representing weeks in the future.

    Note regarding intPeriodsAgo: this is set to a zero if this row is in the same fiscal period as todays date, with positive numbers representing periods already past and negative numbers representing periods in the future.

    Each year, the following code is executed (after making any required changes) to load a new years worth of rows into the calandar table using the feeder table as a base. If it is a 53-week year (and the "extra" week has moved since the last time a 53-week year was loaded) then the feeder table will need to be realigned first using the code at the foot of this article.

    USE EXTRACT;

    DECLARE @startdate datetime, @fy int, @fyname char(7), @days int

    --

    -- Set the parameters below to the required values

    --

    -- SET @startdate = '2007-09-30'

    SET @startdate = (SELECT MAX(pkdatDate)+1 FROM tblFinancialCalendar)

    -- Comment out one of the above two lines:

    -- use the first if you want to set a fixed start date and use the second if you want to append a new year to the end of the existing table

    SET @fy = 9

    SET @fyname = 'FY09/10'

    SET @days = 371

    -- @days must be set to 364 for a 52 week year, or set to 371 for a 53 week year

    -- ( but check that the additional week is in the correct period in the

    -- CalendarFeeder table first or run prcCalendarFeeder53Setup to reset it )

    --

    -- Load the new year into the main table from the feeder table

    --

    INSERT INTO tblFinancialCalendar

    SELECT

    @startdate-1+pkintSeedDay, -- pkdatDate

    pkintSeedDay, -- intDayOfFY

    CASE WHEN @days=364 THEN intSeedWeekOfPeriod ELSE intSeedWeekOfPeriod53 END, -- intWeeekOfPeriod

    CASE WHEN @days=364 THEN intSeedPeriod ELSE intSeedPeriod53 END, -- intPeriodOfFY

    CASE

    WHEN intSeedPeriod>= 1 and intSeedPeriod<=3 THEN 1

    WHEN intSeedPeriod>= 4 and intSeedPeriod<=6 THEN 2

    WHEN intSeedPeriod>= 7 and intSeedPeriod<=9 THEN 3

    WHEN intSeedPeriod>= 10 and intSeedPeriod<=12 THEN 4

    END, -- intQuarterOfFY

    @fy, -- intFYID

    FLOOR((pkintSeedDay-1)/7)+1, -- intWeekOfFY

    NULL, -- intDayOfWeek (set below)

    NULL, -- intWeeksAgo (reset daily)

    NULL, -- intDaysAgo (reset daily)

    NULL -- intPeriodsAgo (reset daily)

    FROM tblCalendarFeeder

    WHERE pkintSeedDay<=@days;

    --

    -- Update the calandar to set the "day of the week" values

    -- (These are used to prevent issues caused by the DATEPART(dw,xxx) function which gives variable outputs dependent on language

    -- and other settings that may be in effect at a per-user level)

    --

    SET DATEFIRST 7; -- Our financial weeks run from Sunday to Saturday

    UPDATE tblFinancialCalendar SET intDayOfWeek=DATEPART(dw,pkdatDate);

    --

    -- Add or update new year into the Financial Year Names table

    --

    DELETE FROM tblCalendarFinancialYearNames WHERE pkintFYID=@fy;

    INSERT INTO tblCalendarFinancialYearNames

    SELECT

    @fy,

    @fyname;

    The table can now be joined to any date elsewhere in the database to obtain fiscal period data.

    Each night the "DaysAgo", "WeeksAgo" and "PeriodsAgo" columns are recalculated using the following code:.

    UPDATE tblFinancialCalendar SET

    intWeeksAgo=DATEDIFF(wk,pkdatDate,GETDATE());

    UPDATE tblFinancialCalendar SET

    intDaysAgo=DATEDIFF(dy,pkdatDate,GETDATE());

    UPDATE tblFinancialCalendar SET

    intPeriodsAgo=(SELECT COUNT(DISTINCT intFYID*100+intPeriodOfFY)-1

    FROM tblFinancialCalendar b

    WHERE pkdatDate BETWEEN tblFinancialCalendar.pkdatDate AND GETDATE())

    WHERE tblFinancialCalendar.pkdatDate<=GETDATE();

    UPDATE tblFinancialCalendar SET

    intPeriodsAgo=(SELECT -COUNT(DISTINCT intFYID*100+intPeriodOfFY)+1

    FROM tblFinancialCalendar b

    WHERE pkdatDate BETWEEN GETDATE() AND tblFinancialCalendar.pkdatDate)

    WHERE tblFinancialCalendar.pkdatDate>GETDATE();

    These columns can then be used to select data from tables to which the calandar has been joined, for reporting perposes. For example, you could select all data from the previous period by adding a "WHERE intPeriodsAgo=1" clause.

    Finally, here is the code that I use to reset the 53rd week if it has moved since the last time a 53-week year was added:

    DECLARE @period int,@cut int;

    --

    -- Set the following parameter before running

    --

    SET @period=11;

    -- Enter the period which is to have the extra 5th week (should be one of 1,2,4,5,7,8,10 or 11)

    SET @cut=(SELECT MAX(pkintSeedDay) FROM tblCalendarFeeder WHERE intSeedPeriod=@period);

    CREATE TABLE tblCalendarFeeder2

    (pkintSeedDay INT NOT NULL PRIMARY KEY,intSeedPeriod INT NOT NULL,intSeedWeekOfPeriod INT NOT NULL);

    UPDATE tblCalendarFeeder SET intSeedPeriod53=NULL,intSeedWeekOfPeriod53=NULL;

    INSERT INTO tblCalendarFeeder2 SELECT pkintSeedDay,intSeedPeriod,intSeedWeekOfPeriod FROM tblCalendarFeeder;

    UPDATE tblCalendarFeeder SET

    intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b

    where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut),

    intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b

    where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut)

    WHERE tblCalendarFeeder.pkintSeedDay<=@cut;

    UPDATE tblCalendarFeeder SET

    intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b

    where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7),

    intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b

    where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7)

    WHERE tblCalendarFeeder.pkintSeedDay>@cut+7;

    UPDATE tblCalendarFeeder SET intSeedPeriod53=@period,intSeedWeekOfPeriod53=5 WHERE intSeedPeriod53 IS NULL;

    DROP TABLE tblCalendarFeeder2;

  • Ryan C. Price

    Ten Centuries

    Points: 1150

    Jeff Moden (9/28/2009)


    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 - did you count my rambling on about week numbers as one of those recommendations ?

    if so, you're making me feel all guilty and I'll have to go off and try and find a copy of that database in an archive somewhere.....

    /Ryan

  • corder

    Ten Centuries

    Points: 1275

    I've seen several replies about using a table to store all of the data that I might need. I have explored this as an option and I definitely think it is a possibility. As Joe Celko pointed out my approach is not SET based and SQL server does like its sets. I try to use them when I can and if you can implement this idea with sets I'd say that's a great way to go. I'll probably even do a table implementation myself.

    One thing missing from the table suggestions is the idea that the starting day of the week could change. The starting day could be Sunday for one company and Monday for another company. You could still build a table to take this into account. Let me know if I'm missing something and the tables do take this into account.

    The comment from Ryan Price is also interesting. I'd like to see some performance metrics on date range joins.

    In the end, I came up with a little function that worked for me and wanted to share it since I had a lot of trouble finding much out there. I'm going to share my function for getting a Period (like a month), which is also procedural based. For a table implementation maybe my functions could be modified to populate a table. Sounds like I or someone could even do a comparison article on function based, set based, and various join methods. That could be fun.

    Thanks everyone for your comments. Keep them coming.:-)

  • Jeff Moden

    SSC Guru

    Points: 995177

    Ryan C. Price (9/29/2009)


    Jeff Moden (9/28/2009)


    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 - did you count my rambling on about week numbers as one of those recommendations ?

    if so, you're making me feel all guilty and I'll have to go off and try and find a copy of that database in an archive somewhere.....

    /Ryan

    Heh... I guess my comment worked. Good folks are coughing up some good stuff. Thank you one and all.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply