SQL Server: How Many Work/Week Days In Date Range
I had the need to calculate how many week days existed in an arbitrary range of dates today. I Googled for a while but didn't find anything I liked and I really didn't want to iterate over the entire range and count. For what it's worth, this is what I hacked out.
2 RETURNS INT
3 AS
4 BEGIN
5
6 -- If the start date is a weekend, move it foward to the next weekday
7 WHILE datepart(weekday, @start_date) in (1,7) -- Sunday, Saturday
8 BEGIN
9 SET @start_date = dateadd(d,1,@start_date)
10 END
11
12 -- If the end date is a weekend, move it back to the last weekday
13 WHILE datepart(weekday, @end_date) in (1,7) -- Sunday, Saturday
14 BEGIN
15 SET @end_date = dateadd(d,-1,@end_date)
16 END
17
18 -- Weekdays are total days in perion minus weekends. (2 days per weekend)
19 -- Extra weekend days were trimmed off the period above.
20 -- I am adding an extra day to the total to make it inclusive.
21 -- i.e. 1/1/2008 to 1/1/2008 is one day because it includes the 1st
22 RETURN (datediff(d,@start_date,@end_date) + 1) - (datediff(ww,@start_date,@end_date) * 2)
23
24 END
It is called like this:
The function trims off any weekend days from the start and end of the period since they aren't going to count towards our total anyway. It then counts the total days and subtracts 2 days for each weekend that occurs in the range. I am adding an extra day to the total to make it inclusive. For instance, 1/1/2008 to 1/1/2008 is one day because it includes the 1st.
The function does NOT exclude holidays. If you have a holiday table, you can count the number of dates in that table who fall between the start and end dates and are a weekday "datepart(weekday, @holiday_date) not in (1,7)" and then subtract the holidays from the total before returning it.
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.
(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
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.
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))
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