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 |