The Bones of SQL - The Calendar Table

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    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

  • Jo Pattyn

    SSC-Dedicated

    Points: 31399

    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'

  • funbi

    SSCarpal Tunnel

    Points: 4992

    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?

  • Stephen Grimshaw

    Say Hey Kid

    Points: 678

    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 2015 2861

    16 Dec 2015 2862

    17 Dec 2015 2863

    18 Dec 2015 2864

    19 Dec 2015 2865

    20 Dec 2015 2865

    21 Dec 2015 2865

    22 Dec 2015 2866

    23 Dec 2015 2867

    24 Dec 2015 2868

    25 Dec 2015 2868

    26 Dec 2015 2868

    27 Dec 2015 2868

    28 Dec 2015 2868

    29 Dec 2015 2868

    30 Dec 2015 2869

    31 Dec 2015 2870

    01 Jan 2016 2871

    02 Jan 2016 2871

    03 Jan 2016 2871

    04 Jan 2016 2871

    05 Jan 2016 2872

  • andrew.mosey

    SSC Veteran

    Points: 274

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    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

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • jfogel

    SSCarpal Tunnel

    Points: 4135

    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

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    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]

  • Peter Juhl

    SSC Enthusiast

    Points: 104

    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?

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    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

  • timwell

    SSCertifiable

    Points: 5054

    EDIT: comment removed. Missed previous comment.

  • Yev.d

    SSC Eights!

    Points: 969

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

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