Bones of SQL - Practical Calendar Queries

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Comments posted to this topic are about the item Bones of SQL - Practical Calendar Queries

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Darko Martinović

    Hall of Fame

    Points: 3545

    Excellent! I accidentally put just one star instead of all five. Sorry. There is no way to correct?!

    Right now I'm in a hurry, but if I catch this time next week I sent you a couple of suggestions for reflection.

  • Jeff Moden

    SSC Guru

    Points: 996831

    Great follow up to your first article on the subject, Bob. There's nothing like coded examples to educate. Well done and 5 stars in my book.

    --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)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good article, thank you.

  • ScottPletcher

    SSC Guru

    Points: 98484

    Good article, but there's a much easier way to calc the nth given day of a given month. Also, btw, Thanksgiving is the fourth Thursday of the month not necessarily the last Thursday (see year 2012, for example).

    Here's the code to calc Thanksgiving for any year with no table lookups, just very fast mathematical calcs. Change "2014" to whatever year you need. [Obviously the 7th of any month is the last possible date for a given day of the week, since by day 8 at least one day will have repeated.] And another sample day calc just to show how easily adaptable the code is.

    --Thanksgiving (4th Thursday of Nov)

    SELECT DATEADD(DAY, -DATEDIFF(DAY, 3, last_possible_first_day) % 7 + 21, last_possible_first_day) AS desired_date

    FROM (SELECT CAST('20141107' AS date) AS last_possible_first_day) AS date_calc1

    --3rd Monday in June 2016 (as an example)

    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, last_possible_first_day) % 7 + 14, last_possible_first_day) AS desired_date

    FROM (SELECT CAST('20160607' AS date) AS last_possible_first_day) AS date_calc1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 996831

    ScottPletcher (10/13/2016)


    Btw, Thanksgiving is the fourth Thursday of the month not necessarily the last Thursday (see year 2012, for example).

    I missed that on the first read. Thanks, Scott.

    --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)

  • whenriksen

    SSCarpal Tunnel

    Points: 4752

    Another benefit to using the calendar table rather than the Select DateAdd, performance is much better when used for filtering/grouping.

    One request I received at my work was to see all of the labor hours grouped by Fiscal Pay Periods. Since the first fiscal pay period starts before the fiscal year, I had to write a lengthy expression to calculate it. Trying to group by that expression would have performed horribly.

    Wes
    (A solid design is always preferable to a creative workaround)

  • ScottPletcher

    SSC Guru

    Points: 98484

    whenriksen (10/13/2016)


    Another benefit to using the calendar table rather than the Select DateAdd, performance is much better when used for filtering/grouping.

    One request I received at my work was to see all of the labor hours grouped by Fiscal Pay Periods. Since the first fiscal pay period starts before the fiscal year, I had to write a lengthy expression to calculate it. Trying to group by that expression would have performed horribly.

    I don't see how that would be the case. Couldn't you just use simple math to calc the pay periods, then join to the pre-calc'd dates? How would that perform worse than having to repeatedly join to a calendar table?

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • whenriksen

    SSCarpal Tunnel

    Points: 4752

    ScottPletcher (10/13/2016)


    I don't see how that would be the case. Couldn't you just use simple math to calc the pay periods, then join to the pre-calc'd dates? How would that perform worse than having to repeatedly join to a calendar table?

    My fiscal year starts on 5/1. Pay periods start on Monday and contain two weeks. Fiscal pay period #1 can end on the first or second Sunday in May. This also means that the first fiscal pay period will include some days from the previous fiscal year. Most years contain 26 pay periods, but some contain 27. Labor data is collected daily (by time code)

    By adding the result as a new column, I can do a single join, laborDate = calendarDate, and then group on my Payroll Period End Date. Running that calculation on our labor data (millions of rows) would be very inefficient, even more so once filtering and grouping are considered. You can use Oct 9, 2016 as seed date if you want to test it. Writing the expression once was enough for me.

    PayrollPeriodEndDate DateName

    2016-10-09 Saturday, October 08 2016

    2016-10-09 Sunday, October 09 2016

    2016-10-23 Monday, October 10 2016

    2016-10-23 Tuesday, October 11 2016

    I'd like to see the impact of storing the calendar table In-Memory. Has anyone tried that yet?

    Wes
    (A solid design is always preferable to a creative workaround)

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Jeff Moden (10/13/2016)


    ScottPletcher (10/13/2016)


    Btw, Thanksgiving is the fourth Thursday of the month not necessarily the last Thursday (see year 2012, for example).

    I missed that on the first read. Thanks, Scott.

    Serious DOH! on my part. Thanks for pointing it out.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    I think Whenriksen summed it all up when he said "Writing the expression once was enough for me." While working out calculations to answer a question is intellectually challenging, it can also be time wasting.

    The whole point of the calendar table is to simplify queries by precalculating values. Once the table is in place, it doesn't matter that the formula used to calculate them was suboptimal. It DOES matter that your calculation be correct. (Thanksgiving... grrrr.)

    Also, although I haven't done time trials, I have no trouble believing that sometimes a simple index seek can take less time than a complex calculation against every row. Especially when the seek is against a table as skinny as a calendar table. Logarithms anyone?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Hey Jeff, could you send me private message with your email? Your box is always overflowing.

    Also, I need to know where to send you payment for the totally spontaneous (ahem) and unrehearsed endorsement earlier. 😀

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • ScottPletcher

    SSC Guru

    Points: 98484

    whenriksen (10/13/2016)


    ScottPletcher (10/13/2016)


    I don't see how that would be the case. Couldn't you just use simple math to calc the pay periods, then join to the pre-calc'd dates? How would that perform worse than having to repeatedly join to a calendar table?

    My fiscal year starts on 5/1. Pay periods start on Monday and contain two weeks. Fiscal pay period #1 can end on the first or second Sunday in May. This also means that the first fiscal pay period will include some days from the previous fiscal year. Most years contain 26 pay periods, but some contain 27. Labor data is collected daily (by time code)

    By adding the result as a new column, I can do a single join, laborDate = calendarDate, and then group on my Payroll Period End Date. Running that calculation on our labor data (millions of rows) would be very inefficient, even more so once filtering and grouping are considered. You can use Oct 9, 2016 as seed date if you want to test it. Writing the expression once was enough for me.

    PayrollPeriodEndDate DateName

    2016-10-09 Saturday, October 08 2016

    2016-10-09 Sunday, October 09 2016

    2016-10-23 Monday, October 10 2016

    2016-10-23 Tuesday, October 11 2016

    I'd like to see the impact of storing the calendar table In-Memory. Has anyone tried that yet?

    Isn't it the same basic technique? Starting with the 14th of the month (by which time at least one, but only one, pay period date will have occurred) and back up to the first actual pay period date in that month based on a known/"base" pay period date, from which you subtract the calculated days.

    After determining one pay period date, you simply add 14 days to determine future pay period dates.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher

    SSC Guru

    Points: 98484

    The Dixie Flatline (10/13/2016)

    Also, although I haven't done time trials, I have no trouble believing that sometimes a simple index seek can take less time than a complex calculation against every row. Especially when the seek is against a table as skinny as a calendar table. Logarithms anyone?

    Wow, that's a fascinating statement. Computers are known for doing billions of certain mathematical calcs per second (graphics chips hit trillions). Under no circumstances I know of can a computer do billions of logical I/Os in anything close to one second.

    Also, many of the calendar tables I've seen are not really that skinny. They often contain a description of the date ("Thanksgiving Day"/"Week End"/etc.), the date in several different formats ("October 7, yyyy"/"Oct 7 yyyy"/etc.) and so on. Not saying yours do, or that they contain all of those column, but it's fairly common.

    Finally, I think the basic calc for the nth given day of a month is actually straightforward. Use a known base date of the same day of the week and adjust another date based on the very simple fact that every 7 days that day repeats, ad infinitum.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • whenriksen

    SSCarpal Tunnel

    Points: 4752

    ScottPletcher (10/14/2016)


    Wow, that's a fascinating statement. Computers are known for doing billions of certain mathematical calcs per second (graphics chips hit trillions). Under no circumstances I know of can a computer do billions of logical I/Os in anything close to one second.

    My (admittedly wide) calendar table has 55k rows. I highly doubt using it versus an expression would add billions of I/Os to the query.

    For me, the performance aspect also includes how well the solution will perform when used by other developers or with different parameter values. The consistency of including it in my calendar table ensures a consistent usage and therefore a consistent performance expectation whether used by me (the author) or a junior developer getting their feet wet.

    I suppose the calc could be wrapped into a udf to accomplish the same thing, but I expect that would hurt performance more than a simple join, (even without the billions of IOs ;-))

    Wes
    (A solid design is always preferable to a creative workaround)

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

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