Count the Number of Weekend Days between Two Dates

  • carsten.saastamoinen

    SSC Veteran

    Points: 204

    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.

  • kevin.ricords

    SSC Rookie

    Points: 29

    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

  • carsten.saastamoinen

    SSC Veteran

    Points: 204

    "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.

  • Jeff Moden

    SSC Guru

    Points: 996863

    carsten.saastamoinen - Thursday, August 23, 2018 9:54 AM

    "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.

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • dbeggs57

    SSC-Addicted

    Points: 462

    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
     @SDate

    datetime = '1900-01-01',
    @EDate datetime = '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 Beggs

    Dan Beggs

  • ScottPletcher

    SSC Guru

    Points: 98495

    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 ca1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 996863

    dbeggs57 - Thursday, August 23, 2018 2:12 PM

    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
     @SDate

    datetime = '1900-01-01',
    @EDate datetime = '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 Beggs

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • dbeggs57

    SSC-Addicted

    Points: 462

    Jeff Moden - Thursday, August 23, 2018 5:31 PM

    dbeggs57 - Thursday, August 23, 2018 2:12 PM

    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
     @SDate

    datetime = '1900-01-01',
    @EDate datetime = '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 Beggs

    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
    ;

    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

  • Jeff Moden

    SSC Guru

    Points: 996863

    dbeggs57 - Thursday, August 23, 2018 6:01 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,

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996863

    ScottPletcher - Thursday, August 23, 2018 4:24 PM

    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 ca1

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996863

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996863

    carsten.saastamoinen - Thursday, August 23, 2018 9:54 AM

    "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.

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996863

    MartJ - Thursday, August 23, 2018 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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996863

    @adam 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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • carsten.saastamoinen

    SSC Veteran

    Points: 204

    On my pc I have the following cpu and duration times from Profiler. I was writing earlier, that I would use different methods depending of number of rows I was expecting to return in most situations. This output from 2018-3-1 to 2018-3-20. With a longer period the result will be different. In a system for a hotel if will be ok to expect few days when creating the hotel bill.

    My first comparisons was the original statement in the article and not a modified one. And in this statement IN (7,1) was used. So I was using IN (7,1). And in the original statement there was using another way of creating a tally table.