Numbers of weekdays in a date range in TSQL

Question

This is harder than it looks. I need a function that calculates the numbers of a given weekday in a date range. I don't want any loops or recursive SQL. There are millions of examples doing just that. I need a fast function for calculation.

Input of the function will be weekday, fromdata, todate

-- counting fridays
set datefirst 1
SELECT dbo.f_countweekdays(5, '2011-07-01', '2011-07-31'),
dbo.f_countweekdays(5, '2011-07-08', '2011-07-15'),
dbo.f_countweekdays(5, '2011-07-09', '2011-07-15'),
dbo.f_countweekdays(5, '2011-07-09', '2011-07-14')

Expected result:

5, 2, 1, 0

Answer 1

create function dbo.f_countweekdays
(
  @DOW int, 
  @StartDate datetime, 
  @EndDate datetime
) 
returns int
begin
  return
  ( select datediff(wk, T2.St, T2.En) -
           case when T1.SDOW > @DOW then 1 else 0 end -
           case when T1.EDOW < @DOW then 1 else 0 end
    from (select datepart(dw, @StartDate),
                 datepart(dw, @EndDate)) as T1(SDOW, EDOW)
      cross apply (select dateadd(d, - T1.SDOW, @StartDate),
                          dateadd(d, 7 - T1.EDOW, @EndDate)) as T2(St, En))
end