Greg Snidow (1/15/2013)
dwain.c My personal preferences in cases like this is to combine the calendar (time slot) generation with the Tally table
Yep, mine is to always keep things as separate and granular as possible so as to make troubleshooting easier, then combine only when necessary. It's funny how folks perceive the same thing in different ways. I'm thinking maybe your way results in a little less memory usage.
I also through in a CROSS APPLY to create TimePeriod, which significantly shortens the code.
I've not really used CROSS APPLY, or any APPLY for that matter, since I'm mainly hitting DB2, but you guys sure do some nifty stuff with it. Thanks Dwain, I always learn something from your posts.
Greg - Thanks for the compliment!
I understand your notion of granularity and agree that it is sound. I'm not sure if you ever heard of a programming language called APL (in case not: http://en.wikipedia.org/wiki/APL_(programming_language))? I worked for a number of years in it and it is known to be cryptic and famous for its one liners. I was involved frequently in competitions to solve a problem in the single, shortest line of code possible. Perhaps my roots are showing. [I'm a strong believer that my experience using it has allowed me to much easier embrace the concept of sets as employed by SQL - but that's another story.]
On the other hand, granularity might be to a certain extent in the eye of the beholder. For example if you often have to work with generated calendars, the construct may become familiar enough to you that you find it sufficiently granular to stand alone.
Paul White (http://www.sqlservercentral.com/Authors/Articles/Paul_White/46040/) has a couple of excellent articles on using APPLY that are a must read.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St