Numbers of weekdays in a date range in TSQL

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

@Mikael Eriksson has got a wonderful idea, but his implementation seems a bit overcomplicated.

Here’s what I’ve come up with (and I’d like to stress that it is based on the solution by @Mikael, to whom the main credit should go):

ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
  RETURN (
    SELECT
      DATEDIFF(wk, @StartDate, @EndDate)
      - CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
      - CASE WHEN DATEPART(dw, @EndDate)   < @Dow THEN 1 ELSE 0 END
      + 1
  )
END

---

UPDATE

As Mikael has correctly noted in his answer’s comment thread, in order for the above solution to work correctly the DATEFIRST setting must be set to 7 (Sunday). Although I couldn’t find this documented, a quick test revealed that DATEDIFF(wk) disregards the actual DATEFIRST setting and indeed returns the difference in weeks as if DATEFIRST was always set to 7. At the same time DATEPART(dw) does respect DATEFIRST, so with DATEFIRST set to a value other than 7 the two functions return mutually inconsistent results.

Therefore, the above script must be amended in order to account for different values of the DATEFIRST setting when calculating DATEDIFF(wk). Happily, the fix doesn’t seem to have made the solution much more complicated than before, in my opinion. Judge for yourself, though:

ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
  RETURN (
    SELECT
      DATEDIFF(wk, DATEADD(DAY, -@@DATEFIRST, @StartDate),
                   DATEADD(DAY, -@@DATEFIRST, @EndDate))
      - CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
      - CASE WHEN DATEPART(dw, @EndDate)   < @Dow THEN 1 ELSE 0 END
      + 1
  )
END

^{Edited: both -@@DATEFIRST % 7 entries have been simplified to just -@@DATEFIRST, as someone suggested here.}