Bones of SQL - Practical Calendar Queries

Question

Bones of SQL - Practical Calendar Queries

Viewing 7 posts - 31 through 36 (of 36 total)

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

ScottPletcher SSC Guru Points: 101087 More actions · Answer 1

For non-work days, I agree a table is best, and as I stated above, I have tables holding non-work dates (only). Such a table is naturally much smaller that one with all dates in it. I could certainly see creating a work days table as well too if you needed it, we just almost never need it for the type of processing that we do.

As to complexity, certainly one would use functions to hide the complexity of the date calcs and to avoid having to repeat the logic (and thus risk logic errors). I didn't feel functions were necessary just for this discussion, as I just just trying to demonstrate a more efficient alternative. Either way, if you have junior developers that can't follow a date displacement and adjustment calculation after being shown a few examples of how they work, then you have far bigger problems than whether or not to do all date calcs using a calendar table.

Edit:

Indeed, we use functions to do almost all work-date-related things now, such as determining the nth working/shipping day from today, or days ago, for example.

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

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

There are a lot of users (and developers) who, while competent in their own fields, are going through a learning curve for SQL. There is no reason not to make their job as simple as possible. Comments about the skill levels of the people someone else has to support are about as productive telling someone that they work for a jerk.

However, I missed that you were using a table for non-workdays only.

I would enjoy reading an article on your approach, and SSC can always use more content. Since not everyone takes the time to read the discussions posted after an article, why don't you do a short write-up of how your system works and the advantages you gain from it?

__________________________________________________

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

Jacque.Murrell SSC Enthusiast Points: 116 More actions · Answer 3

I love this, I learn as much from the articles as I do the discussions. Great article.

Bill Talada SSChampion Points: 11954 More actions · Answer 4

Calendar tables are extremely useful for manufacturing companies and also for accounting departments that have their own tax calendar that could be added into this Julian calendar for easy cross reference. Along with the Julian DayNo of 1 to 365, I would suggest adding a WorkDayNo of about 1 to 250 for each year to simplify visualizing project time-spans without adding up a bunch of ones.

Dennis Wagner-347763 SSC Eights! Points: 945 More actions · Answer 5

Nice article.

I think one of the most powerful uses of a calendar table is joining with the calendar to avoid cursors. I've seen far too many RBAR cursors written to loop through months or even years of dates that could have been avoided by a simple join with a calendar table.

ans328 SSC Enthusiast Points: 165 More actions · Answer 6

Can you give a brief explanation of the nested date functions?

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

ans328 wrote:

Can you give a brief explanation of the nested date functions?

It's just the use of functions as operands (parameters) for other functions. Something like the following pseudo-code...

Function3( Function1(somevalue) ,Function2(somevalue) )

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