I wanted a quick and easy way to determine working days for a given date range. I use this most often in BI/reporting applications specifically when dealing with trending and forecasting but I would assume it has a host of various applications. In the hope that is the case, I thought I would throw it out here for the benefit of all.
Prerequisites
I want to account for both weekends AND scheduled days off (holidays). To accomplish that, you will need a simple “holidays” table in the DB to do the final filter against. Here is the structure I used:
[ID] [INT] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[DATE] [smalldatetime] NOT NULL,
CONSTRAINT [PK_ctrl_Holidays] PRIMARY KEY CLUSTERED
THE CODE
To get the working days, I built a stored proc in SQL Server that accepted a start and an end date. My calculator is inclusive, meaning the start and end days are counted. The process is straightforward enough that you could easily exclude one or both of those if needed.
The SP accepts two parameters: @startDate & @endDate, both of type smalldatetime. Here is the high-level process flow:
- Build a temp table to hold each date in the range and a bit indicating if it is a work day.
- Determine if start day is a weekend day (using DATEFIRST = 1, Monday) and if it is not, add it to the table
- Next, loop while the date is less than the end date specified inserting all dates into the table (Nothing to do with the work day bit is processed, it is NULL at this point)
- After looping to create each date, then update the temp table to flag non-work days (in this case DATEPART dw equals 6 or 7 (Saturday or Sunday if DATEFIRST equals 1).
- Then take a COUNT of work days from the temp table and subtract it from the count of days falling in the requested range from the Holiday table. There you have it.
From there you can see the meat of the SP below.
DECLARE @BusinessDays INT
DECLARE @dayTracker TABLE (calendarDate datetime, isWorkDay bit);
SET DATEFIRST 1
-- insert our starting date
IF DATEPART(DW, @startDate) = 6 OR DATEPART(DW, @startDate) = 7
INSERT INTO @dayTracker
VALUES (@startDate, 0);
ELSE
INSERT INTO @dayTracker
VALUES (@startDate, 1);
-- add dates into table from start to end date
WHILE (SELECT MAX(calendarDate) FROM @dayTracker) < @endDate
INSERT INTO @dayTracker
SELECT DateAdd(dd, 1, MAX(calendarDate)), NULL FROM @dayTracker
-- update table to tag work days
UPDATE @dayTracker
SET isWorkDay = CASE
WHEN DatePart(dw, calendarDate) IN (6, 7)
THEN 0
ELSE 1
END
WHERE isWorkDay IS NULL
SELECT @WorkDays = COUNT(*)
FROM @dayTracker
WHERE isWorkDay = 1;
SELECT @BusinessDays = @WorkDays - COUNT(*)
FROM ctrl_Holidays h
WHERE h.[DATE]
BETWEEN @startDate AND @endDate;
RETURN @BusinessDays;
