SQL BI

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.

In the real world:

• The Holidays CTE could be replaced by a real tabl with user-inserted data.
• The temporary [#Calendar] coule 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.

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