Friday, September 19, 2008

Using SQL To Find Work Days In Date Range II

In Using SQL To Find Work Days In Date Range, we created our fn_GetWorkDaysInRange user defined function in Microsoft SQL Server 2005. However, through our research into VB.NET and other simpler algorithms if you have been reading along, if we can do it better why not learn how.

So not to leave well enough alone, here is the code listing our fn_GetWorkDaysInRange revisited:

ALTER 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))
-- variables used in processing
DECLARE @workDays int, @sign int

-- 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))

-- set initial value of work days result based on include start date value
-- we use sign so that end dates older than start return negative work days
-- for work days to come up as positive value no matter what, sign usage can be replace by 1
SET @workDays = CASE @includeStartDate WHEN 0 THEN 0 ELSE
WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
ELSE Case @sign When 0 Then 1 Else @sign End

-- while end date is not equal to start date add sign (-1/1) number of days
-- and add to work days total if not a weekend
WHILE DateDiff(dd, @startDate, @endDate) <> 0
SET @startDate = DateAdd(dd, @sign, @startDate)
SET @workDays = @workDays +
WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
ELSE @sign

-- return working days result to caller
RETURN @workDays
The number of lines look very similar, but if you closely inspect this new version there are many changes/simplifications. The table variable is no longer needed, removing further dependence on new version(s) of SQL. The logic is reduced to while loop with two execution lines: increment/decrement date value; add to work days total if the new date value is a working day. The extra code is for readability of case logic.

To have this reflect how clean it really is, we can abstract out the logic for weekend which gets rid of the parameters and set statements for weekend day along with extensive case logic. We could then extend that separate function named something like fn_IsNotWeekDay to include logic to check date against our holiday table returning a bit flagging weekend/holiday. With CLR based user defined functions like we explored using VB.NET, this logic can be as complex as we are capable of coding in either SQL or .NET.

Until the next learning adventure.

Keep the code alive!

Related Articles/References:

No comments: