Calendar Table Function

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Comments posted to this topic are about the item Calendar Table Function

  • olatidoye

    Grasshopper

    Points: 23

    Hello

    what is a containt of table holiday

    i m begining in database

  • steve.tarry

    SSC Enthusiast

    Points: 132

    this is a great function. thanks for posting.

    In response to the previous reply, if you want to ignore the Holiday table you can use the following (Feel free to substitute the variable values but this will currently produce the calendar for the current year only).

    It's practically the same script with a minor tweak to remove the Holiday table.

    DECLARE @StartDate DATE = DATEADD(Year, DATEDIFF(Year, 0, GETDATE()), 0);

    DECLARE @EndDate DATE = DATEADD(Year, DATEDIFF(Year, 0, GETDATE())+1, 0)-1;

    SELECT DATEADD(DAY,tt.RID-1,@StartDate)

    , tt.RID

    , DATEADD(DAY,tt.RID-1,@StartDate) AS [Date]

    , DATEPART(quarter,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Quarter]

    , DATEPART(dayofyear,DATEADD(DAY,tt.RID-1,@StartDate)) AS [DayofYear]

    , DATEPART(WEEK,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekofYear]

    , DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Year]

    , DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Month]

    , DATEPART(DAY,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Day]

    , DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Weekday]

    , DATENAME(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [MonthName]

    , DATENAME(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekdayName]

    , (RIGHT(

    REPLICATE('0',(4)) +

    CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)),0)

    ,(4)

    )+

    RIGHT(

    REPLICATE('0',(2)) +

    CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)),0)

    ,(2)

    )

    ) AS [Vintage]

    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RID]

    FROM sys.all_objects WITH (NOLOCK)

    ) tt

    WHERE DATEADD(DAY,tt.RID-1,@StartDate) <= @EndDate;

  • RunnerIE

    SSChasing Mays

    Points: 615

    Great Post - This will save me a lot of effort !

    I'm still SS2005 - The icing on the cake would be to see the ISO week calculated as well, to have the option to start the week on a Monday, and to have weekend dates show up as non working by default without having to put them in the HOLIDAY table...

    I'm also guessing that this could then be easily tailored to calculate the 'Datediff_WORKING_DAYS' between two dates ???

    And for the grand finale - how could I setup location-specific holidays for a corporate environment (i.e. St. Patrick's day is a holiday in Ireland only, etc.)?

    Thanks, Brian.

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Unfortunately, SQL Server 2005 doesn't support ISO Week within DatePart. Some logic could be inserted to allow for this in the function, but the concern would be performance. This function was written to be fast and effecient with minimal logic. If I were to tackle that problem, I would create a separate function just for that purpose and call it within this Calendar Function.

    For the question on DateDiff Working Days, you can use windowing, a technique of adding a row number to the result set through RANK or ROW_NUMBER. This part of the result could be used to determine number of business days between two dates. There are 9 business days between 1/1/2015 and 1/15/2015:

    SELECT ROW_NUMBER() OVER (ORDER BY RID) AS [RowNumber], *

    FROM dbo.CALENDAR('20150101','20150115')

    WHERE IsBusinessDay = 1 And [Date] Between '20150101' AND '20150115'

    If you want specific holidays, your holiday table could contain a column for an Owner. In your example, the Owner would be 'IRE'. Next, adjust the function to accept an Owner parameter and use this in the join predicate for the Holidays table:

    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RID]

    FROM sys.all_objects WITH (NOLOCK)

    ) tt LEFT OUTER JOIN

    dbo.HOLIDAYS rh WITH (NOLOCK) ON DATEADD(DAY,tt.RID-1,@StartDate) = rh.[CALENDER_DATE] And rh.[Owner] = @Owner

  • RunnerIE

    SSChasing Mays

    Points: 615

    Perfect!

    I'll tailor the [IsBusinessDay] field to ignore Sat / Sun values and I'm sorted.

    Thanks, Brian.

  • RunnerIE

    SSChasing Mays

    Points: 615

    Forgot to include the updated [IsBusnessDay] for what it is worth:

    WAS:

    , CASE WHEN rh.[CALENDER_DATE] IS NULL THEN 1 ELSE 0 END AS [IsBusinessDay]

    NOW (Excludes days 1:Sun and 7:Sat)

    , CASE WHEN rh.[CALENDER_DATE] IS NULL AND NOT DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) = 1 AND NOT DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) = 7 THEN 1 ELSE 0 END AS [IsBusinessDay]

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Remember that the day of the week and the name of the week day are included in the function. This way, you can easily exclude these days. These columns are returned as Weekday and WeekdayName.

  • ScottPletcher

    SSC Guru

    Points: 98327

    I don't like the extra overhead of generating hundreds of rows every time when you only need a dozen or two.

    A "standard" sequential-numbers tally table can easily handle those types of requests, generating only the required output rows:

    --Last Day in each month:

    SELECT DATEADD(DAY, -1, DATEADD(MONTH, t.tally, start_date)) AS Date

    FROM (

    SELECT CAST('20140101' AS date) AS start_date

    ) AS control_dates

    INNER JOIN tally t ON

    t.tally BETWEEN 1 AND 12

    ORDER BY Date

    --Last Thursday in each month:

    SELECT DATEADD(DAY, -DATEDIFF(DAY, known_base_Thursday, last_day_of_month) % 7, last_day_of_month) AS Date

    FROM (

    SELECT CAST('20140101' AS date) AS start_date

    ) AS control_dates

    INNER JOIN tally t ON

    t.tally BETWEEN 1 AND 12

    CROSS APPLY (

    SELECT 3 AS known_base_Thursday,

    DATEADD(DAY, -1, DATEADD(MONTH, t.tally, start_date)) AS last_day_of_month

    ) AS assign_alias_names

    ORDER BY Date

    --First and Third Monday in each month:

    SELECT DATEADD(DAY, -DATEDIFF(DAY, known_base_Monday, [7th_day_of_month]) % 7 + days_to_add, [7th_day_of_month]) AS Date

    FROM (

    SELECT CAST('20140101' AS date) AS start_date

    ) AS control_dates

    INNER JOIN tally t ON

    t.tally BETWEEN 1 AND 12

    CROSS APPLY (

    SELECT 0 AS known_base_Monday,

    DATEADD(DAY, 6, DATEADD(MONTH, t.tally - 1, start_date)) AS [7th_day_of_month]

    ) AS max_possible_first_Monday

    CROSS JOIN (

    SELECT 0 AS days_to_add UNION ALL --0=first Monday, 14=3rd Monday.

    SELECT 14

    ) AS additional_output_days

    ORDER BY Date

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

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    A tally table would work well. That is basically what I am building with the sub-select against sys.all_objects. The overhead is there to return a larger data set, but for our application of the function, the cost was negligible.

    I appreciate the point and it may help others decide if this is the appropriate solution for their application or they may want to take a different route.

  • joe.booth

    SSC Veteran

    Points: 241

    I contributed a similar UDF to return holidays for any given year. Basically, the holidays are computed based on rules (such as the same date every year, the 4th Thursday in a month), etc.

    SQL Calendar

    http://www.sqlservercentral.com/scripts/Date+Manipulation/74302/

    This might be a nice addition to your work

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Thanks, Joe. That is a great add to this process. It would be nice to calculate the holidays rather than persist them and have a yearly task to add the next year.

  • Jeff Moden

    SSC Guru

    Points: 995641

    RunnerIE (1/7/2015)


    I'm still SS2005 - The icing on the cake would be to see the ISO week calculated as well,

    Please see the following article for an easy formula to incorporate.

    http://www.sqlservercentral.com/articles/T-SQL/97910/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Thanks Joe for sharing.

  • RunnerIE

    SSChasing Mays

    Points: 615

    Works a charm. Thanks!

Viewing 15 posts - 1 through 15 (of 40 total)

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