The Bones of SQL - The Calendar Table

Question

Post reply

The Bones of SQL - The Calendar Table

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Yev, in this instance a CROSS JOIN would have produced the same result.

I just don't make it a practice to join to subqueries (ctes, derived tables).

__________________________________________________

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

andrew.mosey Old Hand Points: 324 More actions · Answer 2

The Dixie Flatline (9/8/2016)
Steven and Andrew: Obviously I didn't invent the calendar table, this article was just to reintroduce it. There can be many variations on the concept. Thanks for adding yours.

No problems, happy to share and thanks for starting the conversation.

I'm always looking for additional columns to add to my d_date table, currently looking at additional string descriptive columns.

I look forward to your approach to holidays. In Australia we have different holidays in each state, and in some cases different holidays in different parts of a state (eg a Horse Race is just Melbourne), so this adds quite a degree of complexity when there's a requirement to pro-rata monthly forecasts to the day based upon the holidays in the cost centre.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Nicely done, Bob. Can't wait for part 2 to come out.

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

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

corey lawson Hall of Fame Points: 3773 More actions · Answer 4

Gen. Pulaski Day in Chicago is a recognized holiday in Chicagoland, too... so add a Locale or AreaOfEffect column? 😀

Good Friday was a "enterprise" business day off when I worked for company owned by Merrill Lynch, but that could also be because the NYSE is closed on that day as well.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

corey lawson (9/8/2016)
Good Friday was a "enterprise" business day off when I worked for company owned by Merrill Lynch, but that could also be because the NYSE is closed on that day as well.

I am in finance and have that too. Also supported are "early close" days when certain markets close early, e.g. US Bond Markets, usually the day before a holiday. I haven't gone so far as to propose introducing time into our holiday calendar management but there are use cases that have had me considering it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

corey lawson Hall of Fame Points: 3773 More actions · Answer 6

but thanks for the article. It's a good start I'm always wishing the database devs had added something like it before I came along, not the least the various app vendors! Instead there is was all the pretzle bending and wheel reinventions / tech debt that'll never really be replaced but just gimped along year after year.

Connie SSC-Addicted Points: 483 More actions · Answer 7

Excellent article!

When I started my current job, almost 6 years ago, the first thing I did was create a company calendar which has many of the columns you've listed in your post and many others. Without a company calendar, reports would have to be manually re-run and SQL Agent jobs would run for incorrect date ranges. By joining to the company calendar, we always generate the right reports/jobs for the right dates.

This year, we'll have a 13-month year as we're changing our financial year to line up with the calendar year. So it's about time, now that the days are getting cooler, for me to confirm the company holidays (sometimes sightly different than bank/national holidays). Then add the extra month onto this financial year, then get next year loaded. This is much simpler when the financial year isn't changing... 😉

If your business doesn't use a calendar, I strongly recommend that you start working on it now. It really makes life easier for DBA's as well as SQL Devs and BI people.

Peter Juhl SSC Enthusiast Points: 104 More actions · Answer 8

Thank you! It's working now. And now I'll delve into the T-SQL syntax documentation to figure out how it works! The "with" and "over" are new to me so this is a good learning opportunity.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Thanks again, all.

On the subject of varying holidays in multiple locales, I haven't pushed the concept beyond what I mentioned with having a separate column. However, I have an idea for handling that which I will try to code and test over the weekend. Speaking of which, I hope you all have a great one.

__________________________________________________

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

Dan.Hill 14306 SSC Enthusiast Points: 116 More actions · Answer 10

The link at the bottom of "The Bones of SQL - The Calendar Table" article, for "The bones of SQL - The Holiday Table", seems to be a dead link. I notified the Webmaster, but have not received a response and the link is still "not good". Was anyone able to access the "Holiday Table" article and download the resource material? If so, I would like to obtain a copy, if possible. Thank you.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

I think the link was premature. The article on the Holidays table should be out next week. Sorry I didn't include both in one article, but that would have run really long.

__________________________________________________

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

Dan.Hill 14306 SSC Enthusiast Points: 116 More actions · Answer 12

Dan.Hill 14306

SSC Enthusiast

Points: 116

September 9, 2016 at 9:42 am

#1900097

Excellent! Thank you!

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 13

I appreciate this article reminding us of this technique and the additional discussions around it. I've used it before and found it really helpful for various date calculations, knowing holidays, company holidays, weekend/weekday, week of year, ability to easily group dates into other logical groupings, etc. There are all sorts of uses for a Calendar table. One thing I found helpful was to create a procedure of some sort to easily extend it - adding date ranges as needed. This can be helpful for outlier dates when processing a warehouse or just to extend it further than originally needed or as part of a scheduled job.

I know that I was able to greatly reduce the time/cost of a process that calculated future occurrence dates once we had a calendar table in use.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

The Dixie Flatline (9/9/2016)
I think the link was premature. The article on the Holidays table should be out next week.

Correct. The article at that link won't be visible to the general public until the day that the article is published.

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

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

john.brooking SSC-Addicted Points: 417 More actions · Answer 15

Interesting, thanks! We have a physical calendar table in our data warehouse with some of the same useful columns like day of week and week of year, and a series of "current" indicators. Even more useful for us is that it also includes information about our fiscal calendar, which is different from the standard calendar. This allows us to equate calendar days with the fiscal year and period, and indicate the fiscal month divisions.