Build a Calendar Without Pre-Existing Tables

Introduction

I am unfortunate enough to have to be tasked with writing QlikView scripts instead of being a real programmer, and sometimes, the scripts (I hesitate to call them "apps", because that's not what they are at all, however, Qlik calls them that - what a bunch of bozos) require a "calendar" table that is generated from scratch, and that covers a specific date range.

Background

Up to this point, we had been using a Qlikview script to generate this calendar table. Since Qlikview is the performance pig you might already assume that it is, I decided that I'd create a SQL-based solution and just load the data into Qlikview from SQL. This tip describes the technique I used. To give fair warning, I am not what I would call a SQL wizard, but I know how to get around. More importantly, I know how to use google.

Using the code

With that in mind, here's the scalar function I came up with that feeds my stored procedure. I make frequent use of the DATEPART, DATEADD, DATENAME functions available in SQL at the top of the function to ease typing, and follow that up with merely checking values to determine whether or not the specified date is a federal holiday.

There are essentially three types of holidays - holidays that fall on an nth day of the week (such as MLK day, President's Day, etc), and holidays that occur on a certain day of the month but that are adjusted when that day occurs on a weekend (such as New Year's Day, Christmas, etc). For no other reason than "it just made sense to do it that way", I check for each holiday in the order it appears in the year. Using the function below as a template, you can add additional holidays that are applicable to your project(s). Comments indicate which holiday for which I am checking.

NOTE: Your namespace probably won't be Reference_Tables, so remember to change that to fit your schema.

USE [Reference_Tables]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================================
-- Author:		jms
-- Create date: 18 JAN 2017
-- Description:	Returns date info associated with the specified date range. 
-- =======================================================================================
CREATE PROCEDURE [dbo].[sp_GetCalendar]
    @startDate DATE, -- the start of the date range
    @endDate   DATE -- the end of the date range
AS
BEGIN
    SET NOCOUNT ON;

    -- Delcare a variable that inidcates the fiscal year offset that is added to the 
    -- calendar month to arrive at the associated fiscal date. For the US government, 
    -- that month is October, so we subtract 10 (the integer month value for October) 
    -- from 13, and get 3. Set this value according to your own requirements.
    DECLARE @fiscalOffset int = 3;

    -- Sanity Check - make sure the start date is <= the end date. I don't know if this 
    -- is absolutely necessaary, but why take the chance, right?
    if (@startDate > @endDate)
    BEGIN
        DECLARE @temp date = @startDate;
        SET @startDate = @endDate;
        SET @endDate = @temp;
    END

    -- -----------------------------------------------------------------------------------
    -- Setup the temporary table that contains just the first day of a given month in 
    -- the specified range. We will join to this table to determine the "MonthSerial" 
    -- value.
    -- -----------------------------------------------------------------------------------

    -- Drop the temporary table if it exists. Notice I use a unique name to avoid possible 
    -- collisions with SQL written by programmers that were not so forward-thinking).
    IF OBJECT_ID('tempdb..#tempCalendarJMS') IS NOT NULL
        DROP TABLE #tempCalendar
    CREATE TABLE #tempCalendarJMS (YearMonth VARCHAR(6), MonthSerial int)

    -- Populate the temporary table with yearmonth and monthserial values based on the 
    -- specified date range. We should only have one instance of each yearmonth when we're 
    -- done. To create the table from scratch, we use the technique known as the 
    -- "common table expression" (CTE).

What is a common table expression is? A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. In our case, we are creating DateTime values, and deriving additional properties based on those values, all without having to use an excisting table in the database. Pretty handy, eh?

    -- Define the CTE expression name. 
    ;WITH m AS 
    (
        -- Define the CTE query. We're using the row number to increment dates from the 
        -- @startDate value to the @endDate value.
        SELECT TOP (DATEDIFF(Month, @startDate, @endDate) + 1) m = ROW_NUMBER() OVER (ORDER BY [object_id]) 
        FROM sys.all_objects
    )
    INSERT INTO #temp 
    (
        YearMonth,
        MonthSerial
    )
    -- Define the outer query referencing the CTE name, and adding the derived 
    -- column values.
    SELECT CONVERT(VARCHAR, DATEPART(YEAR,  DATEADD(Month, m-1, @startDate))) + CONVERT(VARCHAR, DATEPART(MONTH, DATEADD(Month, m-1, @startDate))),
           DATEDIFF(MONTH, @startDate, DATEADD(Month, m-1, @startDate)) + 1
    FROM m;

    -- -----------------------------------------------------------------------------------
    -- Get calendar data for every day in the date range. We should have one day for every 
    -- day between and including the specified date range dates. We use another CTE to 
    -- generate records for every day within the specified date range.
    -- -----------------------------------------------------------------------------------
    ;WITH n AS 
    (
      SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
      FROM sys.all_objects
    )
    -- Our outer query is where we use SQL date functions to calculate the values 
    -- required for our specs. As you can see, we have "calendar" info and "fiscal" info. 
    -- Our fiscal year starts in October which would result in any date that occurs in 
    -- October having a fiscal month value of 1. To achieve this, we add 3 to the current 
    -- month. It might be advantageous to declare a variable so that you only need to change it in one place
    SELECT n as [rownumber]
           -- Calendar Date columns
           ,DATEADD(DAY, n-1, @startDate) AS CalendarDate
           ,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS CalendarYear
           ,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS CalendarMonth
           ,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS CalendarQuarter
           ,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As CalendarJulianDay
           ,CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, n-1, @startDate))) + 
            CONVERT(VARCHAR, DATEPART(MONTH, DATEADD(DAY, n-1, @startDate))) AS CalendarYearMonth
           ,CEILING(DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate))/7.0) AS NthCalendarWeek
           -- Fiscal date columns
           ,DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate)) AS FiscalDate
           ,DATEPART(YEAR, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate))) AS FiscalYear
           ,DATEPART(MONTH, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate))) AS FiscalMonth
           ,DATEPART(QUARTER, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate))) AS FiscalQuarter
           ,DATEPART(DAYOFYEAR, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate))) As FiscalJulianDay
           ,CONVERT(VARCHAR,DATEPART(YEAR, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate)))) + 
            CONVERT(VARCHAR,DATEPART(MONTH, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate)))) AS FiscalYearMonth
           ,CEILING(DATEPART(DAYOFYEAR, DATEADD(MONTH, @fiscalOffset, DATEADD(DAY, n-1, @startDate)))/7.0) As NthFiscalWeek
           -- Miscellaneous columns
           ,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName]
           ,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth]
           ,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber
           ,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName
           ,ceiling(DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) / 7.0) As NthWeekday

           -- This function is called to determine if the date is a US federal holiday. If 
           -- you don't need this, simply comment it out. Otherwise, refer to the note 
           -- following this code block to find this function on CodeProject.
           ,dbo.fn_IsHoliday(DATEADD(DAY, n-1, @startDate)) AS IsHoliday

           -- The MonthSerial value ius used in Qlikview to calculate rolling values 
           -- (such as rolling-12, rolling-24). If you don't need this kind of 
           -- functionality, you can comment this line out, but remember to also comment 
           -- out the JOIN clause below, and you can also comment out the first CTE above.
           ,t.MonthSerial AS MonthSerial

    FROM n
    -- If you don't need the MonthSerial column, you can comment out the following line
    LEFT JOIN #temp t ON t.YearMonth = CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, n-1, @startDate))) + CONVERT(VARCHAR, DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)))
END

GO

The code above calls a scalar function called fn_IsHoliday. You can fid that function in this tip: US Federal Holdiays (SQL)

Usage

I've used this stored procedure from C# code, an Excel spreadsheet, and from Qlikview scripts. Essentially, anything that can call a stored procedure can call this one (assuming the caller has the appropriate SQL Server permissions).

Points of Interest

Chuck Norris was born in a log cabin he built with his own hands.

History

01 Feb 2017 - Original submission.