Count the Number of Weekend Days between Two Dates

Question

Count the Number of Weekend Days between Two Dates

Adam Aspin

SSCarpal Tunnel

Points: 4890
More actions
June 30, 2018 at 4:05 am

#371050

Comments posted to this topic are about the item Count the Number of Weekend Days between Two Dates

Viewing 15 posts - 1 through 15 (of 62 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 1

I did not make many tests for edge cases and wrote this only a short proof of concept, but this piece of code would return the same and would be much faster, than your tally-table version, since it needs no access to whatever tables nor pseudo-cursor-behavior (just some little math):

SELECT DATEDIFF(DAY, t.from_date, t.to_date) / 7 * 2 + CASE DATEPART(WEEKDAY, t.from_date) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END + CASE DATEPART(WEEKDAY, t.to_date) WHEN 1 THEN 2 WHEN 7 THEN 1 ELSE 0 END FROM (VALUES ('20180301', '20180430')) t (from_date, to_date) ;

I'm sure, that it will be wrong under some circumstances as starting and ending at weekends, but have no time at the moment to "fix" it

God is real, unless declared integer.

csj Old Hand Points: 376 More actions · Answer 2

Why do it in such a complex way. Use recursion, which also is faster in this situation! Tally is only the fastes way if there are MANY, MANY days - not only 18 or a number like this.

In my exampel with 1270 days your solution takes cpu=500ms and elapsed 200ms and my solution use cpu=31ms and elapsed 26ms on my pc.

SET STATISTICS TIME ON;
GO
WITH TallyTable_CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY StockCode) - 1 AS ID
FROM Data.Stock
),
WeekendList_CTE
AS
(
SELECT CAST(DATEADD(DD, ID, '20180301') AS DATE) AS WeekdayDate
FROM TallyTable_CTE
WHEREDATEPART(dw, DATEADD(DD, ID, '20180301')) IN (1,7) AND
ID <= DATEDIFF(DD, '20180301', '20300430')
)
SELECT COUNT(*) AS WeekendDays
FROM WeekendList_CTE
OPTION (MAXRECURSION 0);
GO
WITH
Days
AS
(
SELECT CAST('20180301' AS DATE) AS WeekdayDate
UNION ALL
SELECT DATEADD(DAY, 1, WeekdayDate)
FROM Days
WHERE WeekdayDate < '20300430'
)
SELECT COUNT(*)
FROM Days
WHEREDATEPART(dw, WeekdayDate) IN (1,7)
OPTION (MAXRECURSION 0);

goran.h.jordanov SSC Journeyman Points: 96 More actions · Answer 3

csj - Thursday, August 23, 2018 1:29 AM
Why do it in such a complex way. Use recursion, which also is faster in this situation! Tally is only the fastes way if there are MANY, MANY days - not only 18 or a number like this.
In my exampel with 1270 days your solution takes cpu=500ms and elapsed 200ms and my solution use cpu=31ms and elapsed 26ms on my pc.
SET STATISTICS TIME ON;
GO
WITH TallyTable_CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY StockCode) - 1 AS ID
FROM Data.Stock
),
WeekendList_CTE
AS
(
SELECT CAST(DATEADD(DD, ID, '20180301') AS DATE) AS WeekdayDate
FROM TallyTable_CTE
WHEREDATEPART(dw, DATEADD(DD, ID, '20180301')) IN (1,7) AND
ID <= DATEDIFF(DD, '20180301', '20300430')
)
SELECT COUNT(*) AS WeekendDays
FROM WeekendList_CTE
OPTION (MAXRECURSION 0);
GO
WITH
Days
AS
(
SELECT CAST('20180301' AS DATE) AS WeekdayDate
UNION ALL
SELECT DATEADD(DAY, 1, WeekdayDate)
FROM Days
WHERE WeekdayDate < '20300430'
)
SELECT COUNT(*)
FROM Days
WHEREDATEPART(dw, WeekdayDate) IN (1,7)
OPTION (MAXRECURSION 0);

Just a note these solutions are datefirst dependent :

CASE WHEN ((DATEPART(dw, WeekdayDate) + @@DATEFIRST) % 7) = 0 THEN 7 ELSE (DATEPART(dw, WeekdayDate) + @@DATEFIRST) % 7 END IN (1, 7)
Instead of just :
DATEPART(dw, WeekdayDate) IN (1,7)

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 4

The correct answer is "I need you to have your Director explain in writing why I need to prioritize your inability to look at a calendar over my other work", but yeah, I suppose that solution works too.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

csj - Thursday, August 23, 2018 1:29 AM
Why do it in such a complex way. Use recursion, which also is faster in this situation! Tally is only the fastes way if there are MANY, MANY days - not only 18 or a number like this.
In my exampel with 1270 days your solution takes cpu=500ms and elapsed 200ms and my solution use cpu=31ms and elapsed 26ms on my pc.
SET STATISTICS TIME ON;
GO
WITH TallyTable_CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY StockCode) - 1 AS ID
FROM Data.Stock
),
WeekendList_CTE
AS
(
SELECT CAST(DATEADD(DD, ID, '20180301') AS DATE) AS WeekdayDate
FROM TallyTable_CTE
WHEREDATEPART(dw, DATEADD(DD, ID, '20180301')) IN (1,7) AND
ID <= DATEDIFF(DD, '20180301', '20300430')
)
SELECT COUNT(*) AS WeekendDays
FROM WeekendList_CTE
OPTION (MAXRECURSION 0);
GO
WITH
Days
AS
(
SELECT CAST('20180301' AS DATE) AS WeekdayDate
UNION ALL
SELECT DATEADD(DAY, 1, WeekdayDate)
FROM Days
WHERE WeekdayDate < '20300430'
)
SELECT COUNT(*)
FROM Days
WHEREDATEPART(dw, WeekdayDate) IN (1,7)
OPTION (MAXRECURSION 0);

I don't see what you're measuring resources with in that code. What are you using to measure resource usage with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MartJ SSC Eights! Points: 829 More actions · Answer 6

I wrote a little blog post on something similar as an example of using a recursive CTE (https://devondba.blogspot.com/search?q=Create+a+table+of+dates+showing+holidays+and+workdays), works quite well to build up a calendar table.

Based on your post I've now written a new post to return a summary secction as well, it also shows totals i.e total days, total complete weekends, total holiday days etc. Along with that I've added a little section on how to do it between two dates: https://devondba.blogspot.com/2018/08/create-table-of-dates-showing-holidays.html

No doubt this the answer isn't perfect either but it was fun doing 🙂

Thanks for the inspiration!

Martyn

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

While I don't necessarily agree with the way things were done in the article, I have to tell you that I never agree with any form of recursion even for small stuff when it comes to incremental counting. Comparing a recursive method to a worse method is NOT justification to use recursion. Please see the following article for more on that subject of the Hidden RBAR involved.
http://www.sqlservercentral.com/articles/T-SQL/74118/

That, notwithstanding, I'll do some testing with all of this because, as with all else in SQL Server, "It Depends". "D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

csj Old Hand Points: 376 More actions · Answer 8

One of the big problems when we are writing SELECT statement is, that there is not only one solution. And we should choose the right solution to a given problem. But always a solution that works even if the problem change. I don't think that we will be in a situation, that we should count million of weekend day. Recursive is the best solution for this problem, when we only count few days, because there is af big different in cpu and elapsed time. We are counting weekend days and I don't think that we should count million of weekend days. Using a Tally-solution could be better for other problems.

And the first solution have the problem, that if we should count millions of days, we should use a table - in the example Data.Stock - with at least the same number of rows, that we have days between start and end day. So the first statement could give the wrong result, if number of days between start and end day changed drastic.

And use of memory for the recursive solution is not a problem, when we only have few days. It could be a bigger problem to use a lot of cpu time. And the memory is only used for 25 ms and not for 25 ms * 20.

So make solutions that works correct even if we have big changes in the prerequisites!!!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

csj - Thursday, August 23, 2018 7:31 AM
One of the big problems when we are writing SELECT statement is, that there is not only one solution. And we should choose the right solution to a given problem. But always a solution that works even if the problem change. I don't think that we will be in a situation, that we should count million of weekend day. Recursive is the best solution for this problem, when we only count few days, because there is af big different in cpu and elapsed time. We are counting weekend days and I don't think that we should count million of weekend days. Using a Tally-solution could be better for other problems.
And the first solution have the problem, that if we should count millions of days, we should use a table - in the example Data.Stock - with at least the same number of rows, that we have days between start and end day. So the first statement could give the wrong result, if number of days between start and end day changed drastic.
And use of memory for the recursive solution is not a problem, when we only have few days. It could be a bigger problem to use a lot of cpu time. And the memory is only used for 25 ms and not for 25 ms * 20.

I agree... not counting a "million of weekend day". But, you cannot predicate the usage of the code nor some of the side effects. For example, you might not think that code that uses 31 ms of CPU time and 26 seconds of duration is bad... until someone uses that code 40,000 times in an hour.

If you look at the charts in the article I provided a link for, you'll see the "Red Skyrocket" line that shows just how bad a recursive CTE that counts actually is even for tiny amounts of rows.

Every bit helps avoid the "Death by 1,000 cuts" that so many systems experience. Justification of "easy" poor programming methods due to a low rowcount is a bad habit to get into. And, there are other consequences... if some newbie on the team is looking for a similar solution but for something much bigger and they find recursive code "that works" and they use it, there's going to be hell to pay downstream.

Do it right every time. It just doesn't take that long to do so and is frequently faster to code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

csj Old Hand Points: 376 More actions · Answer 10

But use of memory for the recursive solution is not a problem until someone uses that code 40,000 times in an hour!!!!!!

But the first solution can fail, the second will be more safe!!!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

csj - Thursday, August 23, 2018 7:55 AM
But use of memory for the recursive solution is not a problem until someone uses that code 40,000 times in an hour!!!!!!
But the first solution can fail, the second will be more safe!!!

Understood and much appreciated but you're still justifying the recursive solution by claiming low row counts and low usage.

I'll try to get back to this tonight to try to show what I mean although some of the heavy hitters on this site may beat me to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wim Adams SSC Enthusiast Points: 143 More actions · Answer 12

You could use a system table to generate numbers. Depending on the number of objects and expected date range use one or more crossjoins

DECLARE @startdate Date='20180301'

DECLARE @enddate Date='20180430'

;WITH CTE

AS (SELECT Row_number()

OVER(ORDER BY (SELECT NULL)) AS Regel

FROM sys.objects a

--CROSS JOIN sys.objects b

)

SELECT COUNT(DATEPART(dw,DATEADD(dd, regel, @startdate)))

FROM CTE

WHERE Regel <= DATEDIFF(dd, @startdate, @enddate)

AND DATEPART(dw,DATEADD(dd, regel, @startdate)) IN (1,7)

nkuper SSC Rookie Points: 33 More actions · Answer 13

The following simple code solves the problem more elegantly in my opinion:

SELECT (DATEDIFF(wk, @begin_date, @end_date) * 2)
+(CASE WHEN DATENAME(dw, @begin_date) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, @end_date) = 'Saturday' THEN 1 ELSE 0 END) as weekend_days

carsten.saastamoinen Old Hand Points: 340 More actions · Answer 14

Yes I'm claiming low row counts and will never use Tally-solution with a problem with low row count. But tally is the right solution with high row count! I use different solutions for different problems.

Your solution will - with 40000 executions pr hour - have many concurrent executions and result in much more use of memory than just one execution. My solution will at a certain time have fewer concurrent executions. When I have one execution you will at the same time - avg - have 20 execution. So you must multiply your use of memory by 20.