October 13, 2016 at 12:08 am
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
October 13, 2016 at 5:41 am
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.
October 13, 2016 at 7:27 am
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
Change is inevitable... Change for the better is not.
October 13, 2016 at 8:33 am
Good article, thank you.
October 13, 2016 at 8:50 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 13, 2016 at 9:15 am
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
Change is inevitable... Change for the better is not.
October 13, 2016 at 10:15 am
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)
October 13, 2016 at 10:41 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 13, 2016 at 1:53 pm
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.
PayrollPeriodEndDateDateName
2016-10-09Saturday, October 08 2016
2016-10-09Sunday, October 09 2016
2016-10-23Monday, October 10 2016
2016-10-23Tuesday, 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)
October 13, 2016 at 3:03 pm
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
October 13, 2016 at 3:43 pm
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
October 13, 2016 at 3:52 pm
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
October 14, 2016 at 9:33 am
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.
PayrollPeriodEndDateDateName
2016-10-09Saturday, October 08 2016
2016-10-09Sunday, October 09 2016
2016-10-23Monday, October 10 2016
2016-10-23Tuesday, 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2016 at 9:42 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2016 at 10:56 am
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 36 total)
You must be logged in to reply to this topic. Login to reply