Generate date dimension that support working days calculation

I just wrote a T-SQL query based on CTE that generates support information to calculate working days in a period. Simply look at the WorkingDaySequential measure, which difference between two dates is the number of elapsed working days.

In the real world:

• The Holidays CTE could be replaced by a real table with user-inserted data.
• The temporary [#Calendar] could be a real table, providing necessary data access from DM

and probably much else.

The following code is provided “as is”, without any warranty about its behavior.

UPDATE 2007-11-28 – fixed two missing conditions in the query

SET STATISTICS IO ON 
GO 
SET DATEFIRST 7 -- Default is 7 (US / Sunday)  
GO 

DECLARE @StartYear AS INT DECLARE @EndYear AS INT 
SET @StartYear = 2006;
SET @EndYear = 2015;
WITH Holidays AS (
  -- Italian Holidays     (use year 1900 for recurrencies dates)  
            SELECT Date = CAST('19000101' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19000106' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19000425' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19000501' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19000602' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19000815' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19001101' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19001208' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19001225' AS DATETIME) 
  UNION ALL SELECT Date = CAST('19001226' AS DATETIME) 
  UNION ALL -- Holidays changing date each year  
            SELECT Date = CAST('20060417' AS DATETIME) 
  UNION ALL SELECT Date = CAST('20070409' AS DATETIME)
), 
Years AS (
  SELECT YYYY = @StartYear 
  UNION ALL 
  SELECT YYYY + 1 FROM Years WHERE YYYY < @EndYear
), 
Months AS (
  SELECT MM = 1 
  UNION ALL 
  SELECT MM + 1 FROM Months WHERE MM < 12
), 
Days AS (
  SELECT DD = 1 
  UNION ALL 
  SELECT DD + 1 FROM Days WHERE DD < 31
), 
DatesRaw AS (
  SELECT 
    YYYY = YYYY, 
    MM = MM, 
    DD = DD, 
    ID_Date = YYYY * 10000 + MM * 100 + DD, 
    DateString = CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR), 
    Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 THEN CAST(
      CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR) AS DATETIME
    ) ELSE NULL END 
  FROM 
    Years 
    CROSS JOIN Months 
    CROSS JOIN Days 
  WHERE 
    ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
) 
SELECT 
  DatesRaw.*, 
  DayOfWeek = DATEPART(dw, DatesRaw.Date), 
  CalendarDaySequential = CAST(DatesRaw.Date AS INT), 
  WorkingDay = CAST(
    CASE DATEPART(dw, DatesRaw.Date) 
        WHEN 1 THEN 0 -- Sunday 
        WHEN 7 THEN 0 -- Saturday
        ELSE CASE 
            WHEN recurring.Date IS NULL AND fixed.Date IS NULL THEN 1 
            ELSE 0 END 
    END AS BIT
  ) INTO #Calendar     
FROM 
  DatesRaw 
  LEFT JOIN Holidays recurring ON recurring.Date = DATEADD(
    Year, 
    1900 - YEAR(DatesRaw.Date), 
    DatesRaw.Date
  ) 
  LEFT JOIN Holidays fixed ON fixed.Date = DatesRaw.Date 
ORDER BY ID_Date 
GO 

SELECT *, WorkingDaySequential = ( SELECT COUNT(WorkingDay) FROM #Calendar wd3 WHERE wd3.CalendarDaySequential <= wd1.CalendarDaySequential AND wd3.WorkingDay = 1 ) FROM #Calendar wd1 ORDER BY ID_Date 
GO