Using Recursion and Date Tables to Simplify Date Logic

  • David Beardsley

    Valued Member

    Points: 65

    Comments posted to this topic are about the item Using Recursion and Date Tables to Simplify Date Logic

  • Sean Cowburn

    SSC Veteran

    Points: 211

    Hi David

    I quite like your idea of using CTEs and recursion to build a calendar, but when I ran your CTE(View) I got a MAXRECURSION error, so I edited it a bit and came up with the following:

    WITH Dates AS (

    SELECT

    CAST(CAST(CAST(GETDATE() AS INT) - (DATENAME(DAYOFYEAR, GETDATE() - 1)) AS DATETIME) AS DATE) AS CalendarDate

    UNION ALL

    SELECT

    DATEADD(DAY , 1, CalendarDate)

    FROM Dates

    WHERE CalendarDate <= DATEADD(YY, 1, GETDATE())

    )

    SELECT

    CalendarDate,

    CalendarYear = YEAR(CalendarDate),

    CalendarQuarter = DATENAME(quarter, CalendarDate),

    CalendarMonth = MONTH(CalendarDate),

    CalendarWeek = DATEPART(wk, CalendarDate),

    CalendarDay = DAY(CalendarDate),

    CalendarMonthName = DATENAME(MONTH, CalendarDate),

    CalendarDayOfYear = DATENAME(dayofyear, CalendarDate),

    Weekday = DATENAME(weekday, CalendarDate),

    DayOfWeek = DATEPART(weekday, CalendarDate),

    IsLeapYear = ISDATE(CAST(YEAR(CalendarDate)AS CHAR(4)) + '-02-29')

    FROM Dates

    OPTION (MAXRECURSION 731) --365 + 366 (it is possible that a leap year sneaks in)

    Note the first date isn't a fixed value, but will default to the first day in the year the CTE is called. I also added the IsLeapYear field to the result and limited the recursion to 731 (365 + 366). Now it runs without any errors (at least on my SQL 2008 server).

    Values returned are always for the first day of this year up to and including values for the date one year from now.

    I am going to add this one to my repository.

    Thanks for your effort.

  • nigel.

    SSChampion

    Points: 11627

    Another option without using recursion at all:

    ;with Dates as (

    SELECT TOP (DATEDIFF(dd,'20100101','20110101'))

    CalendarDate = DATEADD(dd,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,'20100101')

    FROM sys.columns A, sys.columns B

    )

    SELECT

    CalendarDate

    FROM

    Dates

  • nigel.

    SSChampion

    Points: 11627

    I use a table valued function which takes two parameters, a start and end date.

    It returns 1 row per day starting on the specified start date up to, but *not* including, the specified end date with various useful (to me anyway) columns.

    It uses a Tally function based on Itzik Ben-Gans' cross join method documented here:

    http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/[/url]

    CREATE FUNCTION [dbo].[Calendar]

    (

    @startDate datetime,

    @endDate datetime

    )

    RETURNS TABLE

    RETURN

    SELECT

    [date] ,

    [weekday] ,

    [weekday2] ,

    [dayname] ,

    [monthname] ,

    d ,

    m ,

    y ,

    ord,

    dayname + ' ' + CAST(d AS VARCHAR(2)) + ord + ' ' + monthname AS LongDate,

    isodate = CONVERT(char(8),date,112),

    iso8601 = CONVERT(NVARCHAR,CAST(date AS DATETIME),126)

    FROM

    (

    SELECT

    [date],

    DATEPART(weekday,date) [weekday],

    (DATEPART(weekday,date) + @@DATEFIRST) % 7 as [weekday2], -- Normalized weekday (0-6) 0=Sat, 6 = Fri

    DATENAME(weekday,date) [dayname],

    DATENAME(MONTH,date) [monthname],

    d = DATEPART(DAY,date),

    m = MONTH(date),

    y = YEAR(date),

    ord = substring('stndrdthththththththththththththththththstndrdthththththththst',(2*datepart(day,date))-1,2)

    FROM

    (

    SELECT

    DATEADD(dd,N-1+DATEDIFF(dd,'19000101',@startDate),'19000101') as date

    FROM

    dbo.Tally(datediff(day,@startDate,@endDate))

    ) s1

    ) s2

    GO

    ---- Tally function

    CREATE FUNCTION [dbo].[Tally](@n int)

    RETURNS TABLE

    AS

    RETURN(

    WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)

    SELECT top (@n) n FROM Nums ORDER BY n

    )

    GO

  • Jeff Moden

    SSC Guru

    Points: 996858

    The Calendar View in the article is based on a "Counting rCTE". Please see the following article for why you should avoid such a thing.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Sean Cowburn

    SSC Veteran

    Points: 211

    Jeff, thanks for the link. Your article made a lot of sense. Not to mention that the code for the tally solution is much more simple and easier to write/remember.

    I suppose I'll have to change my repository again πŸ™‚

  • JonFox

    SSCrazy

    Points: 2157

    I'm not sure about this bit:

    join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = β€˜All’)

    That works fine if @Period != 'All', but as soon as @Period = 'All' the join predicate disappears and you have a cartesian product.

    I think you want:

    join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = β€˜All’ AND cp.CalendarDate IS NULL)

  • GSquared

    SSC Guru

    Points: 260824

    JonFox (12/13/2011)


    I'm not sure about this bit:

    join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = β€˜All’)

    That works fine if @Period != 'All', but as soon as @Period = 'All' the join predicate disappears and you have a cartesian product.

    I think you want:

    join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period) or (@Period = β€˜All’ AND cp.CalendarDate IS NULL)

    or (simpler):

    join CalendarPeriod cp on cp.CalendarDate = OrderDate and (CalendarPeriod = @Period or @Period = β€˜All’)

    [/code]

    On the original article: I suggest avoiding a recursive query like this. Either use a Numbers/Tally table, or an actual Calendar table (not a view). Recursive CTEs are a huge performance hog, and the trade-off is just a few kb of storage and buffer space (for the table), which is trivial on any server built in the last 20 years.

    The uses of Calendar tables (Dates tables) are significant, and it's a very useful tool in just about any database. But be sure you know what performance and other issues you're dealing with.

    If you want a dynamic date-range, a view that uses DateAdd on a Numbers table will give you that much more efficiently than recursion.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • JonFox

    SSCrazy

    Points: 2157

    GSquared (12/13/2011)


    or (simpler):

    join CalendarPeriod cp on cp.CalendarDate = OrderDate and (CalendarPeriod = @Period or @Period = β€˜All’)

    Except, the way the original CalendarPeriod table is structured, I'm pretty sure that doing it that way also returns an incorrect number of rows, since there are multiple rows in CalendarPeriod with the same value for CalendarDate...

  • ShawnTherrien

    Default port

    Points: 1481

    I learned a bit here, but it turns out that my database is already set up in days with various attributes on them and then periods with their own attributes.

    I use the Itzik Ben-Gans/Jeff Moden (I think I read Jeff's article first, so I'm not sure who I should credit here) Numbers/Tally method to generate dates.

    Thank you. I enjoyed this article and this discussion.

  • Jeff Moden

    SSC Guru

    Points: 996858

    Sean Cowburn (12/13/2011)


    Jeff, thanks for the link. Your article made a lot of sense. Not to mention that the code for the tally solution is much more simple and easier to write/remember.

    I suppose I'll have to change my repository again πŸ™‚

    You bet. Thanks for the feedback, Sean.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996858

    ShawnTherrien (12/13/2011)


    I learned a bit here, but it turns out that my database is already set up in days with various attributes on them and then periods with their own attributes.

    I use the Itzik Ben-Gans/Jeff Moden (I think I read Jeff's article first, so I'm not sure who I should credit here) Numbers/Tally method to generate dates.

    Thank you. I enjoyed this article and this discussion.

    The original method is Itzik's... A lot of us improved it here and there... I just explained it a bit so people would know what it is and how it works. Thanks for the thought, though. I definitely appreciate it, Shawn. πŸ™‚

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • GPO

    SSCarpal Tunnel

    Points: 4556

    Under what circumstances would you use one of these on-the-fly solutions as opposed to a permanent calendar table? I'm probably missing something here - it's not unusual - but the only circumstance that I can think of, in which I'd need an on-the-fly solution, is where I am prevented from creating a permanent calendar table on the server I was using. Because these "utility" tables (tally table is another example) are so useful, I've found that it's usually possible to convince the DBA to let them exist permanently.

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • nigel.

    SSChampion

    Points: 11627

    A situation where the on-the-fly may be useful is when you don't know in advance what range of dates you might need, and don't want 40000+ rows (representing 1-1-1900 to present) in your calendar table.

    You can also use it to get months/daynames etc in other languages:

    SET LANGUAGE 'French'

    SELECT monthname FROM dbo.Calendar('20100101','20110101') WHERE d = 1

    SELECT dayname FROM dbo.Calendar('20100104','20100111')

    SET LANGUAGE 'us_english'

    There are many other ways of doing this. I just like my date/calendar stuff in one place.

  • Jeff Moden

    SSC Guru

    Points: 996858

    GPO (12/14/2011)


    Under what circumstances would you use one of these on-the-fly solutions as opposed to a permanent calendar table? I'm probably missing something here - it's not unusual - but the only circumstance that I can think of, in which I'd need an on-the-fly solution, is where I am prevented from creating a permanent calendar table on the server I was using. Because these "utility" tables (tally table is another example) are so useful, I've found that it's usually possible to convince the DBA to let them exist permanently.

    To add to what Nigel said, I'll certainly have some folks disagree with me but a part of the reason why people use Calendar tables is for speed. The problem is that people sometimes go a bit crazy with how many columns of information they put in their table which makes row length longer which means more reads per date range. With the high speed on-the-fly method first coined by Ben-Gan, you'll typically get something that really does live in memory and also has a very narrow row width (again, for performance) if you only calculate what you need.

    Of course, if you really need to do something with a particular set of columns in a Calendar table over and over and over, it may be better to reevaluate the content of the Calendar table so it's not so all encompassing. For example, I think it's a mistake to store month and day names in such a table because there are some high performance system functions that easily take care of such a thing and hard-stored names won't change automatically like they will with system functions if the language changes.

    On the "row width" thing hurting performance... it's one of the reasons why I never combine a Tally Table with a Calendar Table even if I need to enumerate the days in the Calendar Table. Tally Table base functions would have to read a whole lot more rows if I did and that would slow things down even if they're logical reads.

    {EDIT} Of course, proper indexing will help solve that problem but, basically, requires the duplication of data.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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