The Bones of SQL - The Calendar Table

  • Comments posted to this topic are about the item The Bones of SQL - The Calendar Table

    __________________________________________________

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

  • Great article

    Could you change the where-clauses to use proper date-variables instead of strings

    WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)

    instead of

    WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'

  • Jo Pattyn (9/8/2016)


    Great article

    Could you change the where-clauses to use proper date-variables instead of strings

    WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)

    instead of

    WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'

    Is there anything to be aware of using e.g. '27 February 2016' instead of conversions? Locale/language issues I guess?

  • I have a table that numbers working days and assigns the number of the next working day to any non-working day, so durations can still be calculated for events involving non-working days (in this case any days when the office is closed, not just bank holidays).

    15 Dec 20152861

    16 Dec 20152862

    17 Dec 20152863

    18 Dec 20152864

    19 Dec 20152865

    20 Dec 20152865

    21 Dec 20152865

    22 Dec 20152866

    23 Dec 20152867

    24 Dec 20152868

    25 Dec 20152868

    26 Dec 20152868

    27 Dec 20152868

    28 Dec 20152868

    29 Dec 20152868

    30 Dec 20152869

    31 Dec 20152870

    01 Jan 20162871

    02 Jan 20162871

    03 Jan 20162871

    04 Jan 20162871

    05 Jan 20162872

  • Nice approach. I've achieved similar adapting code by Dusty. I've added in a truckload of columns, including many offsets which are particularly useful for automatically setting prompts in reporting. It also handles manual / rule based control of the financial month which tends to lag the calendar month by a few weeks.

    USE [DWH_Config]

    GO

    /****** Object: Table [control].[g_dwh_control] Script Date: 8/09/2016 9:38:40 PM ******/

    DROP TABLE [control].[g_dwh_control]

    GO

    /****** Object: Table [control].[g_dwh_control] Script Date: 8/09/2016 9:38:40 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [control].[g_dwh_control](

    [category_id] [int] NOT NULL,

    [category_name] [nvarchar](50) NOT NULL,

    [key_id] [int] NOT NULL,

    [key_name] [nvarchar](50) NOT NULL,

    [key_value] [int] NULL,

    [key_valuetext] [nvarchar](50) NULL

    ) ON [PRIMARY]

    GO

    insert into dwh_config.control.g_dwh_control (category_id, category_name, key_name, key_value) values (1, 'Dates', 1, 'FinMonth', 201605)

    insert into dwh_config.control.g_dwh_control (category_id, category_name, key_name, key_value) values (1, 'Dates', 2, 'OpDate', 20160502)

    use DWH

    IF EXISTS(SELECT * FROM sys.tables WHERE object_id = object_id('DWH.common.D_Date') AND NAME ='d_date')

    DROP TABLE DWH.common.D_Date;

    CREATE TABLE DWH.common.D_Date (

    -- Generic Date

    [date_sk] [int] NOT NULL

    , [date_sql] [datetime] NOT NULL

    , [date_name] [varchar](10) NULL

    -- Day

    , [day_no] [tinyint] NOT NULL

    , [day_suffix] [varchar](4) NOT NULL

    , [day_of_week_name] [varchar](9) NOT NULL

    , [day_of_week_shortname] [varchar](9) NOT NULL

    , [day_of_week_no] [int] NOT NULL

    , [day_of_week_in_month] [tinyint] NOT NULL

    , [day_of_year_no] [int] NOT NULL

    -- Week

    , [week_sk] [int] NOT NULL

    , [week_of_year_no] [tinyint] NOT NULL

    , [week_of_month_no] [tinyint] NOT NULL

    , [first_day_of_week] [datetime] NULL

    , [last_day_of_week] [datetime] NULL

    , [week_day_flag] [bit] NOT NULL

    -- Month

    , [month_sk] [int] NOT NULL

    , [month_no] [tinyint] NOT NULL

    , [month_name] [varchar](9) NOT NULL

    , [month_shortname] [varchar](9) NOT NULL

    , [first_day_of_month] [datetime] NOT NULL

    , [last_day_of_month] [datetime] NOT NULL

    -- Quarter

    , [quarter_no] [tinyint] NOT NULL -- 2

    , [quarter_name] [varchar](6) NOT NULL -- Second

    , [quarter_shortname] [varchar](6) NOT NULL -- Q2

    , [first_day_of_quarter] [datetime] NULL

    , [last_day_of_quarter] [datetime] NULL

    -- Year

    , [year_no] [int] NOT NULL

    , [first_day_of_year] [datetime] NOT NULL

    , [last_day_of_year] [datetime] NOT NULL

    -- Cal Dates

    , [cal_day_offset] [int] NOT NULL

    , [cal_week_offset] [int] NOT NULL

    , [cal_month_offset] [int] NOT NULL

    , [cal_quarter_offset] [int] NOT NULL

    , [cal_year_offset] [int] NOT NULL

    -- Op Month / Quarter / Year

    , [op_day_offset] [int] NOT NULL

    , [op_week_offset] [int] NOT NULL

    , [op_month_offset] [int] NOT NULL

    , [op_quarter_offset] [int] NOT NULL

    , [op_year_offset] [int] NOT NULL

    -- Fin Offsets

    , [fin_month_offset] [int] NOT NULL

    , [fin_quarter_offset] [int] NOT NULL

    , [fin_year_offset] [int] NOT NULL

    , [fin_year_name] [varchar](6) NOT NULL -- FY2016

    , [etl_create_date] datetime

    CONSTRAINT [PK_D_Date] PRIMARY KEY CLUSTERED

    (

    [date_sk] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    -- -=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-

    -- common.D_Date

    -- 2/5/16, Andrew Mosey

    -- Based on Initial code from SQL Dusty https://sqldusty.com/2012/04/12/create-date-dimension-script/

    -- Adapted for Reporting

    --DBCC CHECKIDENT (common.D_Date, RESEED, 60000) --In case you need to add earlier dates later.

    DECLARE @tmpDOW TABLE (DOW INT, Cntr INT)--Table for counting DOW occurance in a month

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(1,0)--Used in the loop below

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(2,0)

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(3,0)

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(4,0)

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(5,0)

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(6,0)

    INSERT INTO @tmpDOW(DOW, Cntr) VALUES(7,0)

    DECLARE @StartDate datetime

    , @EndDate datetime

    , @FinMonth datetime

    , @OpDate datetime

    , @Date datetime

    , @WDofMonth INT

    , @CurrentMonth INT

    , @CurrentDate date = getdate()

    SELECT @StartDate = '1990-01-01' -- -- Set The start and end date

    , @EndDate = '2031-01-01'--Non inclusive. Stops on the day before this.

    , @CurrentMonth = 1 --Counter used in loop below.

    SELECT @Date = @StartDate

    SELECT @FinMonth =

    datefromparts(

    substring(cast(key_value as nvarchar(8)), 1, 4),

    substring(cast(key_value as nvarchar(8)), 5, 2),

    1)

    from dwh_config.control.g_dwh_control where category_id = 1 and key_id = 1

    SELECT @OpDate =

    datefromparts(

    substring(cast(key_value as nvarchar(8)), 1, 4),

    substring(cast(key_value as nvarchar(8)), 5, 2),

    substring(cast(key_value as nvarchar(8)), 7, 2))

    from dwh_config.control.g_dwh_control where category_id = 1 and key_id = 2

    WHILE @Date < @EndDate

    BEGIN

    IF DATEPART(MONTH,@Date) <> @CurrentMonth

    BEGIN

    SELECT @CurrentMonth = DATEPART(MONTH,@Date)

    UPDATE @tmpDOW SET Cntr = 0

    END

    UPDATE @tmpDOW

    SET Cntr = Cntr + 1

    WHERE DOW = DATEPART(DW,@DATE)

    SELECT @WDofMonth = Cntr

    FROM @tmpDOW

    WHERE DOW = DATEPART(DW,@DATE)

    INSERT INTO common.D_Date

    (

    [date_sk]

    ,[date_sql]

    ,[date_name]

    ,[day_no]

    ,[day_suffix]

    ,[day_of_week_name]

    ,[day_of_week_shortname]

    ,[day_of_week_no]

    ,[day_of_week_in_month]

    ,[day_of_year_no]

    ,[week_sk]

    ,[week_of_year_no]

    ,[week_of_month_no]

    ,[first_day_of_week]

    ,[last_day_of_week]

    ,[week_day_flag]

    ,[month_sk]

    ,[month_no]

    ,[month_name]

    ,[month_shortname]

    ,[first_day_of_month]

    ,[last_day_of_month]

    ,[quarter_no]

    ,[quarter_name]

    ,[quarter_shortname]

    ,[first_day_of_quarter]

    ,[last_day_of_quarter]

    ,[year_no]

    ,[first_day_of_year]

    ,[last_day_of_year]

    ,[cal_day_offset]

    ,[cal_week_offset]

    ,[cal_month_offset]

    ,[cal_quarter_offset]

    ,[cal_year_offset]

    ,[op_day_offset]

    ,[op_week_offset]

    ,[op_month_offset]

    ,[op_quarter_offset]

    ,[op_year_offset]

    ,[fin_month_offset]

    ,[fin_quarter_offset]

    ,[fin_year_offset]

    ,[fin_year_name]

    ,[etl_create_date]

    )

    SELECT

    CONVERT(VARCHAR,@Date,112) [date_sk], --TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT

    @Date [date_sql]

    , RIGHT('0' + convert(varchar(2),MONTH(@Date)),2) + '/' + Right('0' + convert(varchar(2),DAY(@Date)),2) + '/' + convert(varchar(4),YEAR(@Date)) [date_name]

    , DATEPART(DAY,@DATE) [day_no]

    , CASE

    WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'

    WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'

    WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'

    WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'

    ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'

    END AS [day_suffix]

    , CASE DATEPART(DW, @DATE)

    WHEN 1 THEN 'Sunday'

    WHEN 2 THEN 'Monday'

    WHEN 3 THEN 'Tuesday'

    WHEN 4 THEN 'Wednesday'

    WHEN 5 THEN 'Thursday'

    WHEN 6 THEN 'Friday'

    WHEN 7 THEN 'Saturday'

    END AS [day_of_week_name]

    , CASE DATEPART(DW, @DATE)

    WHEN 1 THEN 'Sun'

    WHEN 2 THEN 'Mon'

    WHEN 3 THEN 'Tue'

    WHEN 4 THEN 'Wed'

    WHEN 5 THEN 'Thu'

    WHEN 6 THEN 'Fri'

    WHEN 7 THEN 'Sat'

    END AS [day_of_week_shortname]

    ,DATEPART(DW, @DATE) AS [day_of_week_no]

    , @WDofMonth [day_of_week_in_month] --Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.

    , DATEPART(dy,@Date) [day_of_year_no] --Day of the year. 0 -- 365/366

    , substring(CONVERT(VARCHAR,@Date,112), 1, 4) + right('0' + cast(DATEPART(ww,@Date) as nvarchar(2)), 2) [week_sk]

    , DATEPART(ww,@Date) [week_of_year_no]--0-52/53

    , DATEPART(ww,@Date) + 1 -

    DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [week_of_month_no]

    , dateadd(day, -DATEPART(DW, @DATE)+1, @DATE) [first_day_of_week]

    , dateadd(millisecond, -3, dateadd(day, -DATEPART(DW, @DATE)+8, @DATE)) [last_day_of_week]

    , CASE DATEPART(DW, @DATE)

    WHEN 1 THEN 0

    WHEN 2 THEN 1

    WHEN 3 THEN 1

    WHEN 4 THEN 1

    WHEN 5 THEN 1

    WHEN 6 THEN 1

    WHEN 7 THEN 0

    END AS [week_day_flag]

    , substring(CONVERT(VARCHAR,@Date,112), 1, 6) [month_sk]

    , DATEPART(MONTH,@DATE) as [month_no] --To be converted with leading zero later.

    , DATENAME(MONTH,@DATE) as [month_name]

    , substring(DATENAME(MONTH,@DATE), 1, 3) as [month_shortname]

    , DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) , 0) [first_day_of_month]

    , DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) +1 , 0)) [last_day_of_month]

    --Calendar quarter

    , DATEPART(qq,@DATE) as [quarter_no]

    , CASE DATEPART(qq,@DATE)

    WHEN 1 THEN 'First'

    WHEN 2 THEN 'Second'

    WHEN 3 THEN 'Third'

    WHEN 4 THEN 'Fourth'

    END AS [quarter_name]

    , 'Q' + CAST(DATEPART(qq,@DATE) as VARCHAR(1)) [quarter_shortname]

    , CONVERT (DATETIME, DATEFROMPARTS (DATEPART(YEAR,@DATE), (DATEPART(qq,@DATE) * 3) - 2, 1)) [first_day_of_quarter]

    , dateadd(millisecond, -3, dateadd(month, 3, CONVERT (DATETIME, DATEFROMPARTS (DATEPART(YEAR,@DATE), (DATEPART(qq,@DATE) * 3) - 2, 1)))) [last_day_of_quarter]

    , DATEPART(YEAR,@Date) as [year_no]

    , DATEFROMPARTS (DATEPART(YEAR,@Date), 1, 1) [first_day_of_year]

    , DATEADD(MILLISECOND, -3, CONVERT(DATETIME,DATEFROMPARTS (DATEPART(YEAR,@Date) + 1, 1, 1))) [last_day_of_year]

    -- Cal Offset

    , DATEDIFF(dd,@CurrentDate,@Date) as [cal_day_offset]

    , DATEDIFF(ww,@CurrentDate,@Date) as [cal_week_offset]

    , DATEDIFF(MONTH,@CurrentDate,@Date) as [cal_month_offset]

    , DATEDIFF(qq,@CurrentDate,@Date) as [cal_quarter_offset]

    , DATEDIFF(YEAR,@CurrentDate,@Date) as [cal_year_offset]

    -- Op Offset

    , DATEDIFF(DAY, @OpDate, @Date) [op_day_offset]

    , DATEDIFF(ww,@OpDate,@Date) [op_week_offset]

    , DATEDIFF(MONTH,@OpDate,@Date) [op_month_offset]

    , DATEDIFF(qq,@OpDate,@Date) [op_quarter_offset]

    , DATEDIFF(YEAR,@OpDate,@Date) [op_year_offset]

    -- Fin Offset

    , DATEDIFF(MONTH,@FinMonth,@Date) [fin_month_offset]

    , DATEDIFF(qq,@FinMonth,@Date) [fin_quarter_offset]

    , DATEDIFF(YEAR,@FinMonth,@Date) [fin_year_offset]

    , 'FY' [fin_year_name]

    , getdate() [etl_create_date]

    SELECT @Date = DATEADD(dd,1,@Date)

    END

  • Shamefully I don't use this sort of stuff and I should. I think there are numerous opportunities in our code for this.

  • Love the topic. Calendar tables are quite useful, and are being considered more and more for use cases beyond the data warehouse.

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

  • Thanks, guys.

    Jo Pattyn: Yes you could and in practice you would. Use of the constants just made the examples more readable for purposes of this article. My apologies if the MM/DD/YYYY format was alien to some readers.

    Funbi: There is nothing inherently superior about either string representation of a date. What is important is that it can be converted to a date datatype.

    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.

    Some other things that might be useful would include having columns for fiscal year, quarter, month, day, and week. Or having columns for Canadian Holidays as well as U.S. holidays. Shameless plug: An article is coming soon that will discuss how to programatically build a holidays table to make the job of flagging holidays easier.

    IWasBornReady: You're who this this article was written for. After you read the next article about holidays, you should go ahead and create your calendar table and play with it, so that you have it ready next time. It really does make date calculations much easier once the table is in place.

    __________________________________________________

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

  • Jo Pattyn (9/8/2016)


    Great article

    Could you change the where-clauses to use proper date-variables instead of strings

    WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)

    instead of

    WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'

    If you wanted to avoid conversion or locale concerns you could use the universal date format YYYYMMDD:

    WHERE CalendarDate BETWEEN '20160227' AND '20160301'

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

  • A calendar table is really a must have in my opinion. In our business, we have to count the duration of activities for both business and non-business hours. Some of these things can begin and end on the same day, or stretch over weeks and even months in some rare cases. A function was ported from Access that would calculate the business time duration in seconds, but that uses a loop and was painfully slow. That was one of those cases where you summon your inner Jeff Moden and cut that out. I use the calendar table to fill the gap between a start and end date/times and perform the calculations that way. The process to populate a table for reporting went from round an hour and a half to less than ten minutes.

    I also use this table when a report has to show date/times even if something didn't happen to report such as a monthly cross tab report with totals for actions. Joining to the calendar table avoids things like endless "if not exists". I recommend that anyone who doesn't use such a table and has loops in their code do what it takes to make the time to learn the benefits of a calendar table.

    Cheers

  • Keep in mind that if you have a data warehouse with a date dimension, you may already have a useful calendar table.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • In trying the code, I got stuck immediately with a syntax error in the last line of the vTally view definition: Incorrect syntax near ')'

    the parens seem to match up, so why is is it failing? I'm on SQL 2012. Is this code using a SQL construct that's supported only on later versions?

  • Peter, thank you for bringing this to my attention. I'm not sure how that happened but there is no excuse for it. For some reason SSC isn't letting me post the entire CREATE VIEW statement, but the last few lines should look like this

    L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)

    SELECT TOP (10000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as N from L5

    GO

    My apologies.

    __________________________________________________

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

  • EDIT: comment removed. Missed previous comment.

  • Fantastic article. Thank you!

    One question, if I may, not directly related to the calendar itself, but to a use of CROSS APPLY instead on CROSS JOIN in the employee missed date query. Is there a particular reason why you used CROSS APPLY?

    I changed it to CROSS JOIN and got the same result, but there must be something I'm missing.

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

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