Calendar Tables II - The Anchor

  • tfifield

    SSCrazy Eights

    Points: 9655

    Comments posted to this topic are about the item Calendar Tables II - The Anchor

  • Jason-299789

    SSC-Insane

    Points: 21601

    Another nice example of using a date table, however when you assign the day name wouldnt you be better removing the Case statement and using the DateName(dw,<date>) function that is embeded in SQL, it removes confusion if a company doesnt use the default DATEFIRST.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Knut Boehnert

    SSCrazy

    Points: 2947

    Nearly excellent.

    Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.

    Suggestion for the date table creation below:

    -- Set Language 'English'

    Set Language 'British'

    -- Set Language 'German'

    -- Set Language 'Spanish'

    -- Set Language 'Italian'

    -- Set Language 'French'

    ---- execute sp_helplanguage -- documentation

    --INSERT INTO CalDay

    --(

    -- DayDate,

    -- NextDay,

    -- DayNum,

    -- NameOfDay

    --)

    SELECT

    -- add days to anchor date, then add number of years to that calculation

    DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate,

    DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay,

    Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum,

    DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDay

    FROM dbo.Numbers JulianDays

    Cross Join

    (

    SELECT

    Number As YearNumber

    FROM dbo.Numbers

    WHERE Number Between 2000 And 2011

    ) Years

    WHERE -- ensure that the result is actually a date (safety check)

    IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1

    AND -- don't generate double, one year always has 365 days

    JulianDays.Number Between 1 and 365

    -- add a day for years modulo by 4, but not by 100, except when modulo by 400

    -- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day

    -- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day

    -- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day

    + (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400))

    --AND

    -- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012

    Order By DayDate

    Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.

    One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.

    Very nice and fun article to read otherwise. I really like "the boss".

  • Nadrek

    SSC-Insane

    Points: 20039

    In memento of the "as compact as possible", change DayID to smalldatetime and, if necessary, start at -32768 (there are exactly 65,536 days available to the SMALLDATETIME data type).

    DayNum should be TINYINT

    NameOfDay should be VARCHAR(9)

    Personally, I don't agree with having NextDay in there, but that's going to depend on what kind of data you're joining to.

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for sharing this with us.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    nice article

    i tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly

  • Jeff Moden

    SSC Guru

    Points: 997128

    alen teplitsky (8/18/2010)


    nice article

    i tried using it today and the only problem was that my source data had hours and minutes and it would have taken too long to figure out a way to join it properly

    I know this is an old post but figured I'd show how easy that join actually is thanks to Todd's forsight in including a "NextDay" column ...

    SELECT whatever

    FROM dbo.yourtable yt

    INNER JOIN dbo.Calendar c

    ON yt.YourDate >= c.DayDate

    AND yt.YourDate < c.NextDay

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997128

    Knut Boehnert (8/16/2010)


    Nearly excellent.

    Two things that made it only good for me is the "typical" fact that the date calculation works only for the US (and Canada). The other is the RBAR in the creation of the date table and the hard coded week day names.

    Suggestion for the date table creation below:

    -- Set Language 'English'

    Set Language 'British'

    -- Set Language 'German'

    -- Set Language 'Spanish'

    -- Set Language 'Italian'

    -- Set Language 'French'

    ---- execute sp_helplanguage -- documentation

    --INSERT INTO CalDay

    --(

    -- DayDate,

    -- NextDay,

    -- DayNum,

    -- NameOfDay

    --)

    SELECT

    -- add days to anchor date, then add number of years to that calculation

    DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime))) As DayDate,

    DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number, Cast('1900-01-01' As datetime))) As NextDay,

    Datepart(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As DayNum,

    DateName(Weekday, DateAdd(yy, Years.YearNumber, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) As NameOfDay

    FROM dbo.Numbers JulianDays

    Cross Join

    (

    SELECT

    Number As YearNumber

    FROM dbo.Numbers

    WHERE Number Between 2000 And 2011

    ) Years

    WHERE -- ensure that the result is actually a date (safety check)

    IsDate(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) = 1

    AND -- don't generate double, one year always has 365 days

    JulianDays.Number Between 1 and 365

    -- add a day for years modulo by 4, but not by 100, except when modulo by 400

    -- deduct 1 or 0 from 1 if modulo by 4 = if leapyear 1 - 0 = 1 --> one extra day

    -- add 1 if modulo by 100/400 = if 1900,2100,2200 --> 1 - 0 + 0 - 1 --> no extra day

    -- if 2000, 2400, 2800 --> 1 - 0 + 0 - 0 --> one extra day

    + (1 - Sign(Years.YearNumber % 4) + Sign(Years.Yearnumber % 100) - Sign(Years.YearNumber % 400))

    --AND

    -- Year(DateAdd(yy, Years.YearNumber-1900, DateAdd(dd, JulianDays.Number-1, Cast('1900-01-01' As datetime)))) < 2012

    Order By DayDate

    Certainly only works if there is dbo.Numbers around (how to at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/). The beauty of above beast is that it handles date names simply by the language that is selected and the week day numbers too.

    One option to clear up the code is to create an intermediate step of a temporary table that holds just the date itself, then pass this temporary table to all the functions involved and pass this result set into CalDay.

    Very nice and fun article to read otherwise. I really like "the boss".

    Your code labels the 1st of January, 2000 as a Monday. Check a calendar and find out that it actually occurred on a Saturday. 😉

    Also, with a Tally or Numbers table, there's just no need for the complexity nor the double hit on the Tally table. Have a look...

    --===== Declare some obviously-named variables and preset them

    DECLARE @StartYear DATETIME,

    @EndYear DATETIME

    ;

    SELECT @StartYear = '2000',

    @EndYear = '2011'

    ;

    --===== Return the mini-date calendar

    WITH

    cteDays AS

    (

    SELECT DayDate = DATEADD(dd,t.N-1,@StartYear)

    FROM dbo.Tally t --works for zero and unit based Tally tables in this case

    WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@StartYear,DATEADD(yy,1,@EndYear))

    )

    SELECT DayDate,

    NextDay = DATEADD(dd,1,DayDate),

    DayNum = DATEPART(dw,DayDate+@@DATEFIRST-1), --Doesn't care what DATEFIRST is set to.

    NameOfDay = DATENAME(dw,DayDate)

    FROM cteDays

    ;

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997128

    Almost forgot... I love the idea of having the NextDay column in there because it makes it so easy to join on dates with times while still being SARGable in the Joins and Predicates. 😉

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Chris Cross

    Grasshopper

    Points: 21

    I was actually asked recently about a calendar table and I've never really been a big fan. Too much admin.

    The request was very similar, a full list of days even if no sales. The analyst that asked said could he create a calendar table and do it that way with a join and I turned him down due to administration, so he asked for another result that would mean it could be dynamic, no matter what dates were put in, past or future.

    So, I worked out a quick function to give a calendar result.

    create function [dbo].[fn_callist](@startdate date , @enddate date)

    returns @results table (caldate date,yearnum int,monthnum int,weeknum int,daynum int,

    monname varchar(12),weekday varchar(20))

    as

    begin

    declare @loop int = 0

    declare @loopend int = 0

    set @loopend=DATEDIFF(d,@startdate,@enddate)

    while @loop<>@loopend+1

    begin

    insert into @results

    select DATEADD(d,@loop,@startdate),0,0,0,0,'',''

    set @loop=@loop+1

    end

    update @results

    set yearnum=DATEPART(yy,caldate),

    monthnum=datepart(m,caldate),

    monname=DATENAME(MONTH,caldate),

    weeknum=DATEPART(WEEK,caldate),

    daynum=DATEPART(d,caldate),

    weekday=DATENAME(WEEKDAY,caldate)

    return

    end

    GO

    It's pretty fast, does the same kind of thing as a table but needs no real admin and gives you full details of the date. Great for when they don't like you adding tables to a DB. Also makes life a little easier should you wish to add some new functionality to it.

  • somillohani

    SSC Rookie

    Points: 35

    Really Nice article..it helped me in a project where i have to manage attendance of employee according to days.

  • JustMarie

    SSCertifiable

    Points: 7771

    I just created a whole slew of calendar tables so this is something that's going in my briefcase here.

    FYL - My calendar tables are wide but still fit on a page. They also have StartDt and EndDt with the EndDt being the very, very, very end of the day in milliseconds because that's how date times work here. The future queries will be easier and faster since that date bracket is already calculated. They also include breakouts of the date values both in numeric and text values, again for ease of getting the info out of the table.

    I will certainly be pointing the reporting people here for info on how best to use the tables. Thanks for the clear examples!

  • sneumersky

    SSCertifiable

    Points: 7667

    Storing the current and previous day as metadata is also common practice (in some circles) with SQL Server Analysis Services (date dimension) and Powerpivot, and it is also demostrated in a few MS white papers. It definitely limits the complexity of some date calculations.

    In Analysis Services, however, you must be mindful that some aggregation rebuilds will be triggered if those aggregations are based on "CurrentDay = 'Y'.

  • Jeff Moden

    SSC Guru

    Points: 997128

    Chris Cross (2/21/2014)


    I was actually asked recently about a calendar table and I've never really been a big fan. Too much admin.

    The request was very similar, a full list of days even if no sales. The analyst that asked said could he create a calendar table and do it that way with a join and I turned him down due to administration, so he asked for another result that would mean it could be dynamic, no matter what dates were put in, past or future.

    So, I worked out a quick function to give a calendar result.

    create function [dbo].[fn_callist](@startdate date , @enddate date)

    returns @results table (caldate date,yearnum int,monthnum int,weeknum int,daynum int,

    monname varchar(12),weekday varchar(20))

    as

    begin

    declare @loop int = 0

    declare @loopend int = 0

    set @loopend=DATEDIFF(d,@startdate,@enddate)

    while @loop<>@loopend+1

    begin

    insert into @results

    select DATEADD(d,@loop,@startdate),0,0,0,0,'',''

    set @loop=@loop+1

    end

    update @results

    set yearnum=DATEPART(yy,caldate),

    monthnum=datepart(m,caldate),

    monname=DATENAME(MONTH,caldate),

    weeknum=DATEPART(WEEK,caldate),

    daynum=DATEPART(d,caldate),

    weekday=DATENAME(WEEKDAY,caldate)

    return

    end

    GO

    It's pretty fast, does the same kind of thing as a table but needs no real admin and gives you full details of the date. Great for when they don't like you adding tables to a DB. Also makes life a little easier should you wish to add some new functionality to it.

    First, I notice this is your first post, so welcome aboard!

    Shifting gears, creating such a function is a great idea but not using a While loop and not using an mTVF (Multi-statement Table Valued Function). I'm on my way to work but I'll be back to show you a much quicker and much less resource intensive method (if someone doesn't beat me to it).

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    I was interested in the fact that you chose a final report that had no dates at all.

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

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