June 30, 2018 at 4:05 am
Comments posted to this topic are about the item Count the Number of Weekend Days between Two Dates
August 23, 2018 at 12:41 am
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.
August 23, 2018 at 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);
August 23, 2018 at 4:17 am
csj - Thursday, August 23, 2018 1:29 AMWhy 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)
August 23, 2018 at 5:24 am
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
August 23, 2018 at 6:50 am
csj - Thursday, August 23, 2018 1:29 AMWhy 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
Change is inevitable... Change for the better is not.
August 23, 2018 at 6:51 am
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
August 23, 2018 at 7:10 am
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
Change is inevitable... Change for the better is not.
August 23, 2018 at 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.
So make solutions that works correct even if we have big changes in the prerequisites!!!
August 23, 2018 at 7:48 am
csj - Thursday, August 23, 2018 7:31 AMOne 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
Change is inevitable... Change for the better is not.
August 23, 2018 at 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!!!
August 23, 2018 at 8:11 am
csj - Thursday, August 23, 2018 7:55 AMBut 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
Change is inevitable... Change for the better is not.
August 23, 2018 at 8:13 am
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)
August 23, 2018 at 8:52 am
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
August 23, 2018 at 9:24 am
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.
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply