Count the Number of Weekend Days between Two Dates
-
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.
-
One more option:
select
[WeekendDays]=
ceiling(datediff(day,dateadd(d, (7-datepart(weekday,@start)), @start),@end)/7.0)+ --count saturdays starting at the first saturday
ceiling(datediff(day,dateadd(d, (8-(datepart(weekday,@start)))%7, @start),@end)/7.0) --count sundays starting at the first sunday -
"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."
You can use exact the same argument for your bad code. If every newbie thinks, that tally-solutions is the right solution to all problems, they make big problems because they get a very slow solution - maybe 20 times slower than better code - and go to hell l!!!!!
I have seen a lot of bad code, because the developers has learned just to use one method - the 'right' metode. A good developer use different metodes for different problems. And design the system to the world, that the system are going to be used in. We are not all Wallmart or MasterCard.
- carsten.saastamoinen - Thursday, August 23, 2018 9:54 AM
Absolutely agreed except that the code in the article isn't what I'd call a "permanent" Tally Solution only because there's no guarantee that an unrelated user table will have rows in it. It's also not a proper solution because the ORDER BY in the ROW_NUM() forces materialization of data from the row source table and it's just not necessary. That's a part of what makes the solution in the article a slower than it could be. Developers do need to learn things like not using recursive methods to simply "count". A bloody WHILE loop can easily outstrip an incremental recursive CTE and that doesn't make it the correct solution either.
On the tally stuff, do you have an example of a proper Tally table solution that actually took 20 times slower? If you do, please post it because 1) I agree that Tally Table solutions are NOT a panacea and 2) I'm always up for code analysis.
In the meantime, I recommend that you dig into a proper Tally Table solution for this problem and then do a real comparison.
--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) -
Adam, thank you for your example, it got me thinking. I didn't have the table you used in the your CTE so I picked a Tally table creator that I got on this site. I decided I would try to display both a week day count and a weekend day count. Some of the other comments got me to try it for a longer period of time so I went for 100 years. Here is what I came up with -
declare
@SDatedatetime = '1900-01-01',
@EDatedatetime = '2000-01-01'
;with
cteTally(RowNum) as
(
select top (100000) n = ROW_NUMBER() over (order by (select null)) - 1
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) i(n)--> 10 rows
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) x(n)--> * 10 rows = 100
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)--> * 10 rows = 1000
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) m(n)--> * 10 rows = 10000
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) e(n)--> * 10 rows = 100000
)
select
Sum(Case When DatePart(dw, DateAdd(dd, RowNum, @SDate)) in (1, 7) Then 0 Else 1 End)as 'WeekDays',
Sum(Case When DatePart(dw, DateAdd(dd, RowNum, @SDate)) in (1, 7) Then 1 Else 0 End)as 'WeekendDays'
from cteTally
where RowNum between 0 and DateDiff(dd, @SDate, @EDate) + 1
;
Thanks for getting me to think !
Dan BeggsDan Beggs
-
There's no need for any tally table nor recursion. A simple mathematical calc is enough. I'm almost sure this is it, although I'm extremely busy and haven't fully tested it yet.
Edit: Corrected for starting day of Sunday.
SELECT from_date, to_date,
days_diff / 7 * 2 /* whole weeks days */ +
CASE
WHEN days_diff % 7 = 0 THEN 0
WHEN from_day = 6 THEN 1
WHEN from_day + days_diff % 7 - 1 >= 6 THEN 2
WHEN from_day + days_diff % 7 - 1 >= 5 THEN 1
ELSE 0
END AS total_weekend_days
FROM ( VALUES ('20180301', '20180430'), ('20180301', '20180429'), ('20180301', '20180428'),
('20180301', '20180304'), ('20180301', '20180303'), ('20180226', '20180302'),
('20180826', '20180902') , ('20180824', '20180902')
) AS dates (from_date, to_date)
CROSS APPLY (
SELECT DATEDIFF(DAY, from_date, to_date) + 1 AS days_diff,
DATEDIFF(DAY, 0, from_date) % 7 AS from_day
) AS ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- dbeggs57 - Thursday, August 23, 2018 2:12 PM
Don't use that. It's one of the reasons why people think that Tally structure solutions are slow,. Your code does the full count from 1 to 100,000 and then filters out all but 36,525 rows instead of creating just the 36,525 rows that are actually needed. If you don't think so, call up the Actual Execution Plan and look at the arrow just to the left of the left most Nested Loop operator and see. Total waste of clock cycles and memory.
The following also has a classic error in it...
where RowNum between 0 and DateDiff(dd, @SDate, @EDate) + 1
Since you started counting at "0", you've already added an extra day. The +1 adds one too many days an gives an incorrect answer for the WeekEndDays return.
Try the following code and note the arrow coming of the leftmost Nested Loop to see that only the required rows are being generated. Even on this trivial number of rows, it allows this code to execute roughly twice as fast and use about half the CPU time (on my little i5 laptop). It also comes up with the correct answer.
DECLARE @SDate DATETIME = '1900-01-01'
,@EDate DATETIME = '2000-01-01'
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,DW(D) AS (SELECT TOP (DATEDIFF(dd,@SDate,@EDate)+1)
DATEPART(dw,DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@SDate))
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
SELECT Weekdays = SUM(CASE WHEN D IN (1,7) THEN 0 ELSE 1 END)
,WeekEndDays = SUM(CASE WHEN D IN (1,7) THEN 1 ELSE 0 END)
FROM DW
;Of course, both solutions have a problem because they rely on what the value of @@DATEFIRST is.
--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) - Jeff Moden - Thursday, August 23, 2018 5:31 PM
Jeff:
Thank you for correcting my code. This gives me something more to think about now. Once I understand all of the parts and how they interact I can try to apply it to my job.
Sincerely,Dan Beggs
- dbeggs57 - Thursday, August 23, 2018 6:01 PM
Thank you for the feedback and for digging into the code. If you have a question about what I posted, please don't hesitate to ask. If I don't answer right away, there are a ton of heavy hitters on this forum that can answer any question that I might answer about 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) - ScottPletcher - Thursday, August 23, 2018 4:24 PM
Ok... now we're talking and that's a perfect example of what I mean by the "Tally table is not a panacea". It does replace the hidden RBAR of an rCTE (Recursive CTE) but even the pseudo-cursor behind the Tally Table (or whatever) causes people to forget about simple date math.
Nicely done, Scott.
For anyone that's looking at Scott's code, the dates in the FROM clause are test dates and not an actual part of the formula.
--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) -
My very first article on this site was about how to find work days (week days, really... no holidays involved). Figured I'd update from that a bit to solve this problem with a kicker or two. Details are in the code, just like they always are. 😀
CREATE FUNCTION dbo.DayTypeCount
/**********************************************************************************************************************
Purpose:
Given a start date and an end date, return the number of week days, weekend days, and total number of days contained
in the range of dates as a table result.Note that this is a high performance iTVF (Inline Table Valued Function) and should not be mistaken as a Scalar
Function (although it can be used as an iSF (inline Scalare Function)) or a slower mTVF (Multi-statement Table Valued
Function).Please see the Programmer's Notes below for additional information.
=======================================================================================================================
Usage Examples:--===== Use with variables/basic syntax
DECLARE @pStartDate DATE = '1900-01-01'
,@pEndDate DATE = '2000-01-01'
;
SELECT * FROM dbo.DayTypeCount(@pStartDate,@pEndDate)
;
Results from above:
StartDate EndDate WeekDays WeekEndDays TotalDays
---------- ---------- ----------- ----------- -----------
1900-01-01 2000-01-01 26090 10435 36525-----------------------------------------------------------------------------------------------------------------------
--===== Use against a table of start and end dates
SELECT dtc.*
FROM dbo.SomeTable st
CROSS APPLY dbo.DayTypeCount(st.StartDateColumn, st.EndDateColumn) dtc
;
=======================================================================================================================
Programmer's Notes:
1. Start and end dates must be in the correct temporal order but no error will occur if they are not and the resulting
negative values will not be correct.
2. Dates with times will be stripped of the time elements.
=======================================================================================================================
References:
Posted question: https://www.sqlservercentral.com/Forums/Topic1972887.aspx
Technique reference: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
=======================================================================================================================
Revision History:
Rev 00 - 23 Aug 2018 - Jeff Moden
- Rewrite of old code to be language independent and independent of @@DATEFIRST settings.
**********************************************************************************************************************/
--===== Define the input parameters (ok if reversed by mistake)
(
@pStartDate DATE
,@pEndDate DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH cte AS
(--==== This will work correctly regardless of what @@DATEFIRST is set to.
-- Note that the CASE statements use a Monday = 0 and Sunday = 6 calculate reference.
SELECT TotalDays = DATEDIFF(dd,@pStartDate,@pEndDate)+1 --Start with total number of days including weekends
,WeekEndDays = DATEDIFF(wk,@pStartDate,@pEndDate)*2 --Subtact 2 days for each full weekend
+ CASE WHEN (DATEPART(dw,@pStartDate)-1+(@@DATEFIRST-1))%7 = 6 THEN 1 ELSE 0 END --If StartDate is a Sunday, Add 1
+ CASE WHEN (DATEPART(dw,@pEndDate )-1+(@@DATEFIRST-1))%7 = 5 THEN 1 ELSE 0 END --If EndDate is a Saturday, Add 1
)--==== Return the results as a inline table
SELECT StartDate = @pStartDate
,EndDate = @pEndDate
,WeekDays = TotalDays-WeekEndDays
,WeekEndDays
,TotalDays
FROM cte
;--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) - carsten.saastamoinen - Thursday, August 23, 2018 9:54 AM
Ok... let me show you what I mean using your example. Of course, the math-only methods are absolutely the best way to do this but, for a moment, we'll digress to the idea of creating a rowset to solve this problem.
Here's a test harness that uses your rCTE and my Inline Tally Table Structure. Before each test, it drops cache and buffers and then runs each test 5 times just to see if there's any nasty compile time problems (there weren't) and to see what the consistency of the runs is.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
--===== carsten.saastamoinen's Recursive CTE ============================================
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
WHERE DATEPART(dw, WeekdayDate) IN (1,7)
OPTION (MAXRECURSION 0);
SET STATISTICS TIME OFF;
GO 5
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
--===== Jeff's Inline Tally Table =======================================================
DECLARE @SDate DATETIME = '20180301'
,@EDate DATETIME = '20300430'
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,DW(D) AS (SELECT TOP (DATEDIFF(dd,@SDate,@EDate)+1)
DATEPART(dw,DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@SDate))
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
SELECT Weekdays = SUM(CASE WHEN D IN (1,7) THEN 0 ELSE 1 END)
,WeekEndDays = SUM(CASE WHEN D IN (1,7) THEN 1 ELSE 0 END)
FROM DW
;
GO 5
Here are the results from SQL Profiler (I still like it) on my little i5 laptop... CPU is in milliseconds and duration is in micro seconds.
The code I wrote is anywhere from 4 to 20 times faster than your rCTE and it settles out to usually 20 times faster. Hmmmm... seems to me that 20 times slower was a big concern of yours. :Whistling:It's also interesting to note that, like I pointed out in the article I sited, your method settles out at using 39,997 reads. That's equivalent to 312 Megabytes of memory IO... for something that was supposed to be all CPU and returns a single value??? :blink:
Speaking of CPU... that 31ms you're so proud of is an eternity for this especially after the code has settled. The Tally method that you've been badmouthing takes less than 1/2 ms (if it was more than that, it would show as 1 ms instead of 0 ms). Being conservative, that's 61 times less CPU than your rCTE.
You also brought up the subject of newbies learning different ways. I absolutely agree... they need to learn the recursive CTE method and compare it to other methods so they know how bad it really is even on small stuff. :sick:
Now, understanding that I've already said that the WHERE IN (1,7) thing isn't the right way to do this and neither is any kind of rowset method for this particular problem, you were saying something about using the exact same argument for my bad code? 😀 Please drive through.
p.s. Do you like pork chops?
--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 - Thursday, August 23, 2018 6:51 AM
Now try it without an rCTE. It'll also be fun. 😀
--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) -
@adam-2 Aspin,
Sorry, Adam... I do honestly appreciate anyone that will step up to bat with an article and share the knowledge they have and I thank you for that but, considering the outcome of this discussion and the review of your code for this article (that you claim is part of your book?), you and your partner might want to consider rewriting certain sections of that book.
--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) -
This was removed by the editor as SPAM
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply