Post reply

Using Recursion and Date Tables to Simplify Date Logic

  • December 14, 2011 at 6:56 am

    #1421248

    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.

    A couple of examples:

    You don't have any special rules about calendars/dates, you just want a range of days. One table (Numbers) then solves both needs, and you don't need a dedicated Calendar table.

    You have a Calendar table, but you need dates outside of its range.

    You're doing complex computations where integers are more efficient than the compound numbers that SQL Server uses to store DateTime data.

    Ordinality of dates matters for the query. E.g.: You need the 5th date, the 10th date, etc., in multiple ranges. In a Calendar table, you'd need to use the Row_Number function or some similar ranking function, but in a Numbers table, the numbers themselves give you exactly what you need. Less math = faster query in this case.

    - 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

Viewing post 16 (of 15 total)

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