Bones of SQL - Practical Calendar Queries

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


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

  • 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

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

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

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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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