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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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