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 recusive 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')
5, 2, 1, 0
I hope someone can help.
@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
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, [email protected]@DATEFIRST, @StartDate), DATEADD(DAY, [email protected]@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
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
An alternative approach is the good old-fashioned data warehouse time dimension, where you have a table with all potential dates in it, along with any useful information you want to filter/count by:
Key ActualDate DayName IsWeekday DayNumberInYear FinancialQuarter 20110101 1 Jan 2011 Saturday 0 1 2011 Q1 20110102 2 Jan 2011 Sunday 0 2 2011 Q1 20110103 3 Jan 2011 Monday 1 3 2011 Q1
Then just join to that table and filter, e.g.
SELECT COUNT(*) FROM date_dimension WHERE ActualDate BETWEEN '1 Jan 2011' AND '3 Jan 2011' AND IsWeekday = 1
If you do date analysis a lot over a known range of dates, this can really speed up and simplify your queries. Whether you know your possible date ranges in advance is the limiting factor on whether this is helpful, really, but it's a useful trick to know about.
This is what I came up with after trying alot of different approches. I did spend a long time on solving it and I was still working on it, when I posted the question. I decided to post it as an answer because of the self-learner badge, although I never got more than 2 points for an answer.
alter function dbo.f_countweekdays ( @day int, @fromdate datetime, @todate datetime ) returns int begin RETURN (SELECT datediff(day, @fromdate, dateadd(week,datediff(week,0,@todate - 1) + CASE WHEN datepart(weekday,@todate) < @day THEN 0 ELSE 1 END,0) + @day - 1) / 7) end