Count the Number of Weekend Days between Two Dates

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4795

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

  • Thomas Franz

    Hall of Fame

    Points: 3535

    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

    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
    WHERE DATEPART(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
    WHERE DATEPART(dw, WeekdayDate) IN (1,7)
    OPTION (MAXRECURSION 0);

  • goran.h.jordanov

    SSC Journeyman

    Points: 96

    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
    WHERE DATEPART(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
    WHERE DATEPART(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: 6352

    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: 994293

    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
    WHERE DATEPART(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
    WHERE DATEPART(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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • MartJ

    Right there with Babe

    Points: 769

    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: 994293

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • csj

    Old Hand

    Points: 376

    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: 994293

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • csj

    Old Hand

    Points: 376

    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: 994293

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Wim Adams

    SSC Enthusiast

    Points: 142

    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

    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

    SSC Enthusiast

    Points: 168

    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 34 total)

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