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
  
INT

Rounds a number down to the nearest integer.

INT ( <Number> )

UNION

Returns the union of the tables whose columns match.

UNION ( <Table>, <Table> [, <Table> [, … ] ] )

ALL
CALCULATE modifier

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

ALL ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

AND ( <Logical1>, <Logical2> )

LEFT

Returns the specified number of characters from the start of a text string.

LEFT ( <Text> [, <NumberOfCharacters>] )

DATEADD
Context transition

Moves the given set of dates by a specified interval.

DATEADD ( <Dates>, <NumberOfIntervals>, <Interval> )

YEAR

Returns the year of a date as a four digit integer.

YEAR ( <Date> )

COUNT

Counts the number of rows in the table where the specified column has a non-blank value.

COUNT ( <ColumnName> )