Wednesday, September 17, 2008

Using SQL To Find Work Days In Date Range

I had a question come up today for Microsoft SQL Server 2005 on how to calculate the number of working/business days between two dates with a requirement that the answer must function in countries where weekend can be variable two days or even one. My first thought was that is simple. Famous last words!

Eight hours of research later, this article covers the calculation of working days in the date range which appears to work quite nicely. Here is a code listing:

CREATE FUNCTION [dbo].[fn_GetWorkDaysInRange]
(
@startDate datetime -- first datetime in range
, @endDate datetime -- last datetime in range (can be in past)
, @includeStartDate bit -- flag to include start date as a work day
, @firstWkndDay int -- first day of weekend (e.g. Day(datetime))
, @lastWkndDay int -- last day of weekend (e.g. Day(datetime))
)
RETURNS int
AS
BEGIN
-- variables used in processing
DECLARE @workDays int, @sign int
DECLARE @table table (calendarDate datetime, isWorkDay bit)

-- parse input and calculate direction of date range
SET @firstWkndDay = Coalesce(@firstWkndDay, 0)
SET @lastWkndDay = Coalesce(@lastWkndDay, @firstWkndDay)
SET @startDate = Coalesce(@startDate, getdate())
SET @endDate = Coalesce(@endDate, @startDate)
SET @sign = Sign(DateDiff(dd, @startdate, @enddate))

-- insert our starting date
INSERT INTO @table
VALUES (@startDate, Case @includeStartDate When 0 Then 0 Else NULL End)

-- add dates into table from start to end date
IF @sign > 0 -- use sign of date difference to determine direction
BEGIN
WHILE (SELECT MAX(calendarDate) FROM @table) < @enddate
INSERT INTO @table
SELECT DateAdd(dd, 1, MAX(calendarDate)), NULL FROM @table
END
ELSE
BEGIN
WHILE (SELECT MIN(calendarDate) FROM @table) > @enddate
INSERT INTO @table SELECT DateAdd(dd, -1, MIN(calendarDate)), NULL FROM @table
END

-- update table to tag work days
UPDATE @table
SET isWorkDay = CASE
WHEN DatePart(dw, calendarDate) IN (@firstWkndDay, @lastWkndDay)
THEN 0
ELSE 1
END
WHERE isWorkDay IS NULL

-- select the working days from our table into return variable
SELECT @workDays = COUNT(calendarDate) FROM @table WHERE isWorkDay = 1

RETURN (@workDays * @sign)
END
As stated above, this function will remove weekend days between the starting and ending range and thus return number of working days. Once we have that result, we can use a query to retrieve our holidays (or alternatively modify the above to take in a country code and lookup the weekend days and holidays returning the net business days).

Here is an example of this function's usage

-- create holidays table for testing data
CREATE TABLE [dbo].[Holidays](
[day] [datetime],
[holiday] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[country] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]

INSERT INTO Holidays
SELECT '12/25/2008', 'Christmas', 'USA'
UNION SELECT '12/26/2008', 'Day After Christmas', 'USA'
-- end creation of table for testing data

DECLARE @workDays int, @holidays int
DECLARE @startDate datetime, @endDate datetime

SET @startDate = '12/15/2008'
SET @endDate = '12/29/2008'

SELECT @workDays = dbo.fn_GetWorkDaysInRange(@startDate, @endDate, 0, 7, 1)

SELECT @holiDays = COUNT([day])
FROM [Holidays]
WHERE [country] = 'USA' AND DatePart(dw, [day]) NOT IN (7, 1)
AND [day] BETWEEN @startDate AND @endDate

PRINT (@workDays)
PRINT (@holidays)
PRINT (@workDays - @holidays)

Results come out 10, 2, and 8 for each of the three print statements, respectively. Exactly what we wanted! It is a joy when it all works.

Hopefully this post will save you as long journey, but leave enough uncharted territory to have a little fun with in customizing to your own environment. I have even played with this myself to replace some logic I was using for determining shop working days, so enjoy. For those of you not on Microsoft SQL Server 2005, please keep in mind that other versions of Microsoft SQL Server that support user defined functions should work. Consequently, for other platforms or versions, the structure of this code can probably be manipulated to work in a stored procedure and/or using temporary table instead of a table variable and likewise for other features used not present in your system. The principles should be the same.

Hope this helps and happy coding!