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