SQL Server: How Many Work/Week Days In Date Range

Not sure if you saw this one. There was another one on CFLib.org already before I posted this, but I didn't care for the solution because it looped over the dates one day at a time, so if you were counting days across several months the iterations could add up and you might wind up with an unexpected performance drain. So I created this one that does it mathematically by week except for the last partial week:

http://cflib.org/udf/countArbitraryDays

I'm not sure how often it would be used, but I figured there might also be cases of say, "we have this meeting on tuesdays and thursdays" so they want to count just those 2 days and could do that by passing in an argument of the days they want to omit like "1,2,4,6,7" to see how many meetings there would be between two dates.

@Ike: I'll keep that one in mind if I ever need a CF solution. My specific problem today dictated that I use a SQL solution though.

Oh np ... Didn't realize that was the need when I read the article. :)

I generaly would us a day table with a is work day field. Then you just count where between start and end and workday. You avoid looping in sql and let it do what it is good at working with sets of data.

@Daniel: How do you populate the table? Also, an explicit table approach would limit your calculations to only the records you had populated.

Uh :) Yes, when I first time faced "CountDayBetween" function in visual basic (which d.m) I improvized just like you. Anyway this is wrong solution, because you iterate. You can mathematically extend this (very valid) function which is mostly prepared by my friend, who is mathematician.. I only optimized it

Shared Function countDaysBetween(ByVal fromDate As Date, ByVal toDate As Date) As Integer
  Dim y1% = fromDate.Year - 1
  Dim y2% = toDate.Year - 1

  Dim ytd% = (y2 - y1) * 365 + y2 \ 4 - y1 \ 4 + y2 \ 400 - y1 \ 400 - y2 \ 100 + y1 \ 100
  Dim dm%() = {0, 0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334}
  '                      +31 +28 +31 +30  +31  +30  +31  +31  +30 +31  +30
  Dim d1%, d2%
  Dim m1% = fromDate.Month
  Dim d% = fromDate.Day
  If lDateTime.Math.isLeapYear(y1 + 1) Then : If m1 > 2 Then : d1 = dm(m1) + 1 + d : Else : d1 = dm(m1) + d : End If
  Else : d1 = dm(m1) + d : End If

  Dim m2% = toDate.Month
  d = toDate.Day
  If lDateTime.Math.isLeapYear(y2 + 1) Then : If m2 > 2 Then : d2 = dm(m2) + 1 + d : Else : d2 = dm(m2) + d : End If
  Else : d2 = dm(m2) + d : End If

  Return ytd + d2 - d1
End Function

Thanks brad,

This helped me a lot. Could you be so kind to explain how to incorporate the holiday table in this function? My knowledge in this matter is a bit limited.

@Fauzi :

If you had a table called "holiday" which contained a reccord for each holiday you wanted excluded from the calculation you would simply need to do a select from that table where the holiday_date was in your date range and was NOT already a weekend. The number of records you found would be subtracted from the previous total.

It would look something like this (untested):

RETURN @prev_total - (SELECT count(1) FROM holiday WHERE holiday_date >= @start_date AND holiday_date <= @end_date AND datepart(weekday, holiday_date) not in (1,7))

this is perfect ... thanks for posting .. In my case I just get End Date ... so just by doing smal tweak I got what I want ...

DECLARE @start_date DATETIME DECLARE @end_date DATETIME

select @end_date = '01/16/2009'
SELECT @start_date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@end_date)-1),@end_date),101)

WHILE datepart(weekday, @start_date) in (1,7) -- Sunday, Saturday
BEGIN
    SET @start_date = dateadd(d,1,@start_date)
END

-- If the end date is a weekend, move it back to the last weekday
WHILE datepart(weekday, @end_date) in (1,7) -- Sunday, Saturday
BEGIN
    SET @end_date = dateadd(d,-1,@end_date)
END

-- Weekdays are total days in perion minus weekends. (2 days per weekend)
-- Extra weekend days were trimmed off the period above.
-- I am adding an extra day to the total to make it inclusive. 
--     i.e. 1/1/2008 to 1/1/2008 is one day because it includes the 1st
SELECT (datediff(d,@start_date,@end_date) + 1) - (datediff(ww,@start_date,@end_date) * 2) AS NumerOfWorkDays

Hi Brad,

Its a cool puzzle. Well I tried and ended with a solution. Please check out my solution below-

[code]

DECLARE @st AS DATETIME = '10/1/2008' DECLARE @Et AS DATETIME = '10/31/2008'

;WITH CTE AS ( SELECT @st Dts , DATEPART(weekday,@st) wk UNION ALL SELECT DATEADD(d,1,Dts) , DATEPART(weekday, DATEADD(d,1,Dts)) wk FROM CTE WHERE DATEADD(d,1,Dts) <= @Et ) SELECT COUNT(1) WeekDaysinGivenPeriod FROM CTE WHERE wk <> 1 AND wk <> 7

[/code]

Regards, Pawan

http://msbiskills.com/