I often receive a very common question during my Comprehensive Database Performance Health Check about how to find Find Business Days Between Dates. Let us see a very simple script for it.
DECLARE @StartDate AS DATE = '2021-07-01', @EndDate AS DATE = '2021-07-31' ;WITH dateCTE AS ( SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday UNION ALL SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday FROM dateCTE WHERE DATEADD(day,1,StartDate) <= @EndDate ) SELECT COUNT(*) WeekDays FROM dateCTE WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun
When you run the script above it will give results as following:
You can specify any other date as a start date and end date and it will give you accurate results. Let me know what you think of this blog post about Find Business Days Between Dates.
Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Index Scans – Good or Bad? – SQL in Sixty Seconds #174
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
Reference: Pinal Dave (http://blog.SQLAuthority.com)
2 Comments. Leave new
The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
…);
INSERT INTO Calendar
VALUES (‘2007-04-05’, 42),
(‘2007-04-06’, 43), — good Friday
(‘2007-04-07’, 43),
(‘2007-04-08’, 43), — Easter Sunday
(‘2007-04-09’, 44),
(‘2007-04-10’, 45); –Tuesday
To compute the business days from Thursday of this week to next Tuesdays:
SELECT (C2.julian_business_nbr – C1.julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = ‘2007-04-05’,
AND C2.cal_date = ‘2007-04-10’;
I’m attempting to use your script, and it’s working fine in my query, but when i attempt to build it into SSRS, it’s saying: ‘Types don’t match between the anchor and the recursive part in column “StartDate” of recursive query “dateCTE”.’
My edits to your code:
–Calculate Business days
DECLARE @WeekDays INT
;WITH dateCTE AS
(
SELECT @Date1 StartDate, DATEPART(WEEKDAY,@Date1) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @Date2
)
SELECT @WeekDays = COUNT(*)
FROM dateCTE
WHERE wkday NOT IN(1,7) — Execluding Sat, Sun