Count the Number of Weekend Days between Two Dates

  • Jeff Moden - Thursday, August 23, 2018 10:47 PM

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

    +(Number of pages in the book)
    😎

  • I agree with Scott, no need for anything but simple math.
    😎
    It is very disappointing when articles with sub-optimal solutions are published on this site but I'm pleased to see responses like this thread when that happens😉 

    Here is a function which is a modification of a function that calculates week days, which I've used for a long time. It does in fact use the same logic as Scott's code, but I must admit that Scott's code is more human readable 🙂
    USE TEEST;
    GO
    SET NOCOUNT ON;
    GO
    CREATE OR ALTER FUNCTION dbo.ITVFN_CALC_WEEKEND_DAYS
    ---------------------------------------------------------------------
    -- Calculate the number of weekend days between and including two
    -- dates.
    -- NOTE: This is a modification of the dbo.ITVFN_CALC_WEEK_DAYS
    --   algorithm.
    ---------------------------------------------------------------------
    -- USAGE:
    -- DECLARE @FROM_DATE DATE = '1900-01-01';
    -- DECLARE @TO_DATE  DATE = '2000-01-01';
    -- SELECT
    --  WED.FROM_DATE
    --  ,WED.TO_DATE
    --  ,WED.WEEK_END_DAYS
    -- FROM dbo.ITVFN_CALC_WEEKEND_DAYS(@FROM_DATE,@TO_DATE) WED
    ---------------------------------------------------------------------
    (
      @FROM_DATE DATE
     ,@TO_DATE  DATE
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
    WITH BASE_CALC AS
    (
      SELECT
       @FROM_DATE AS FD
       ,@TO_DATE AS TD
       ,DATEDIFF(DAY,@FROM_DATE,@TO_DATE) AS FDD
       ,(DATEDIFF(DAY,0,@FROM_DATE) % 7)  AS SWD
    )
    SELECT
      BC.FD AS FROM_DATE
     ,BC.TD AS TO_DATE
     ,(((1 + BC.FDD) / 7) * 2)
      + SIGN(((BC.SWD) + ((1 + BC.FDD) % 7)) - 5)
      + (SIGN(((BC.FDD) % 7)) * (((BC.SWD) + ((BC.FDD) % 7)) / 6)) AS WEEK_END_DAYS
    FROM  BASE_CALC BC;

    And an example of the logic using Scott's sample data set

    USE TEEST;
    GO
    SET NOCOUNT ON;

    ;WITH SAMPLE_DATA(FD,TD) AS
    (
      SELECT
       CONVERT(DATE,X.FD,112) AS FD
       ,CONVERT(DATE,X.TD,112) AS TD
      FROM
      ( VALUES
       ('20180301', '20180430')
       ,('20180301', '20180429')
       ,('20180301', '20180428')
       ,('20180301', '20180304')
       ,('20180301', '20180303')
       ,('20180226', '20180302')
       ,('20180826', '20180902')
       ,('20180824', '20180902') 
      ) X(FD,TD)
    )
    ,BASE_CALC AS
    (
      SELECT
       SD.FD
       ,SD.TD
       ,DATEDIFF(DAY,SD.FD,SD.TD) AS FDD
       ,(DATEDIFF(DAY,0,SD.FD) % 7) AS SWD
      FROM  SAMPLE_DATA  SD
    )
    SELECT
      BC.FD AS FROM_DATE
     ,BC.TD AS TO_DATE
     ,(((1 + BC.FDD) / 7) * 2)
      + SIGN(((BC.SWD) + ((1 + BC.FDD) % 7)) - 5)
      + (SIGN(((BC.FDD) % 7)) * (((BC.SWD) + ((BC.FDD) % 7)) / 6)) AS WEEK_END_DAYS
    FROM  BASE_CALC BC
    ;

    Output

    FROM_DATE TO_DATE  WEEK_END_DAYS
    ---------- ---------- -------------
    2018-03-01 2018-04-30 18
    2018-03-01 2018-04-29 18
    2018-03-01 2018-04-28 17
    2018-03-01 2018-03-04 2
    2018-03-01 2018-03-03 1
    2018-02-26 2018-03-02 0
    2018-08-26 2018-09-02 3
    2018-08-24 2018-09-02 4

  • For final prod code, I'd probably make a couple of other minor adjustments to make the code more inherently clear.  I'm a firm believer in self-documenting code, including clear variable names, whenever possible.  Although, if you get used to always doing calcs based off of SQL's base 0 date (19000101), you get very familiar with 0 being Monday, 1 = Tuesday, etc. 🙂.  [For the record, this code works correctly under any/all DATEFIRST settings.]


    SELECT from_date, to_date,
        days_diff / 7 * 2 /* whole weeks days */ +
        CASE /* remainder days, cannot be more than 2 */
            WHEN days_diff % 7 = 0 THEN 0
            WHEN from_day = Sunday THEN 1
            WHEN from_day + days_diff % 7 - 1 >= Sunday THEN 2
            WHEN from_day + days_diff % 7 - 1 >= Saturday 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,
           5 AS Saturday, 6 AS Sunday
    ) 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."

  • ScottPletcher - Friday, August 24, 2018 8:23 AM

    [For the record, this code works correctly under any/all DATEFIRST settings.]

    So does the function I posted above.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Using a CTE seems overly complex. This is conceptually the same. I also agree with other posters saying that a Tally table may be overkill. The tally table could obviously be derived from any table of a suitable size. I just used the first to hand

    DECLARE @StartDate datetime =  '20180301'
    DECLARE @EndDate datetime = '20180430'

    SELECT COUNT(*) FROM
    (SELECT ROW_NUMBER() over (ORDER BY object_id) AS ID FROM sys.objects) AS Tally
    WHERE ID< DATEDIFF(DD,@StartDate,@EndDate) AND DATEPART(DW,DATEADD(DD, ID, @StartDate)) IN (1,7)
  • Here's my version, also no recursion, with added alias names for more clarity:

    SELECT 
    start_date, end_date,
    full_weeks * 2 +
    CASE WHEN remaining_days = 0 THEN 0
    WHEN start_day_of_week = Sun THEN 1
    WHEN remaining_days + start_day_of_week >= Sun THEN 2
    WHEN remaining_days + start_day_of_week = Sat THEN 1
    ELSE 0 END AS weekend_day_count
    FROM (VALUES
    (CAST('20180301' AS date), CAST('20180430' AS date)),
    ('20201004','20201009'),
    ('20201004','20201010'),
    ('20201004','20201011'),
    ('20201004','20201012'),
    ('20201004','20201013'),
    ('20201004','20201014'),
    ('20201004','20201015'),
    ('20201004','20201016'),
    ('20201004','20201017'),
    ('20201004','20201018')
    ) AS test_dates(start_date, end_date)
    CROSS APPLY (
    SELECT DATEDIFF(DAY, start_date, end_date) + 1 AS total_days,
    DATEDIFF(DAY, 0, start_date) % 7 AS start_day_of_week,
    0 AS Mon, 5 AS Sat, 6 AS Sun
    ) AS calc1
    CROSS APPLY (
    SELECT total_days / 7 AS full_weeks,
    total_days % 7 AS remaining_days
    ) AS calc2

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

  • ScottPletcher wrote:

    Here's my version, also no recursion, with added alias names for more clarity:

    SELECT 
    start_date, end_date,
    full_weeks * 2 +
    CASE WHEN remaining_days = 0 THEN 0
    WHEN start_day_of_week = Sun THEN 1
    WHEN remaining_days + start_day_of_week >= Sun THEN 2
    WHEN remaining_days + start_day_of_week = Sat THEN 1
    ELSE 0 END AS weekend_day_count
    FROM (VALUES
    (CAST('20180301' AS date), CAST('20180430' AS date)),
    ('20201004','20201009'),
    ('20201004','20201010'),
    ('20201004','20201011'),
    ('20201004','20201012'),
    ('20201004','20201013'),
    ('20201004','20201014'),
    ('20201004','20201015'),
    ('20201004','20201016'),
    ('20201004','20201017'),
    ('20201004','20201018')
    ) AS test_dates(start_date, end_date)
    CROSS APPLY (
    SELECT DATEDIFF(DAY, start_date, end_date) + 1 AS total_days,
    DATEDIFF(DAY, 0, start_date) % 7 AS start_day_of_week,
    0 AS Mon, 5 AS Sat, 6 AS Sun
    ) AS calc1
    CROSS APPLY (
    SELECT total_days / 7 AS full_weeks,
    total_days % 7 AS remaining_days
    ) AS calc2

    There seems to be an issue when the week starts on Monday.

    When using ( '20200601', '20200605' ) -- Mon - Fri, the weekend_day_count comes up as 1, when it should be 2

    When using ( '20200601', '20200606' ) -- Mon - Sat, the weekend_day_count comes up as 2, when it should be 1

  • This is a simple math problem, that does not require CTEs , recursion or iteration.

    DECLARE @from_date DATE = '1-Oct-2020';
    DECLARE @to_date DATE = '25-Oct-2020';

    select
    @from_date as From_Date,
    @to_date as To_Date,
    DATEADD(d, -DATEPART(dw, @from_date), @from_date ) as From_Prev_Sunday,
    DATEADD(d, -DATEPART(dw, @to_date), @to_date ) as To_Prev_Sunday,
    DATEDIFF(week , DATEADD(d, -DATEPART(dw, @from_date), @from_date ), DATEADD(d, -DATEPART(dw, @to_date), @to_date ) ) as Num_Sundays

     

    GrayB

  • You should count both saturdays and sundays.

    Buth with your simple math solution, where you only counts sundays, the results is wrong. 2 exampels:

    DECLARE @from_date DATE = '11-Oct-2020'; -- sunday

    DECLARE @to_date DATE = '18-Oct-2020'; -- sunday

    The result is 1.

    And if we try with

    DECLARE @from_date DATE = '11-Oct-2020'; -- sunday

    DECLARE @to_date DATE = '16-Oct-2020'; -- frieday

    If "SET DATEFIRST 1" the result is 1

    If "SET DATEFIRST 7" the result is 0

    So maybe not just a simple solution!

  • It's late in the day, and I'm below my quota of coffee (all too common these days, I really need to rekindle my coffee habit), but here's another, fairly concise approach that should work:

    DECLARE @test_dates TABLE (start_date DATE, end_date DATE);

    INSERT INTO @test_dates (start_date,end_date) VALUES
    ('20180301','20180430'),
    ('20201004','20201009'),
    ('20201004','20201010'),
    ('20201004','20201011'),
    ('20201004','20201012'),
    ('20201004','20201013'),
    ('20180301','20180429'),
    ('20180301','20180428'),
    ('20201004','20201014'),
    ('20201004','20201015'),
    ('20201004','20201016'),
    ('20200601','20200605'),
    ('20200601','20200606'),
    ('20201004','20201017'),
    ('20201004','20201018');

    SELECT start_date,
    end_date,
    weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
    FROM @test_dates
    CROSS APPLY
    (VALUES(DATEDIFF(DAY,6,start_date),DATEDIFF(DAY,6,end_date)))x(x,y);

     

    Cheers!

  • Jacob Wilkins wrote:

    It's late in the day, and I'm below my quota of coffee (all too common these days, I really need to rekindle my coffee habit), but here's another, fairly concise approach that should work:

    DECLARE @test_dates TABLE (start_date DATE, end_date DATE);

    INSERT INTO @test_dates (start_date,end_date) VALUES
    ('20180301','20180430'),
    ('20201004','20201009'),
    ('20201004','20201010'),
    ('20201004','20201011'),
    ('20201004','20201012'),
    ('20201004','20201013'),
    ('20180301','20180429'),
    ('20180301','20180428'),
    ('20201004','20201014'),
    ('20201004','20201015'),
    ('20201004','20201016'),
    ('20200601','20200605'),
    ('20200601','20200606'),
    ('20201004','20201017'),
    ('20201004','20201018');

    SELECT start_date,
    end_date,
    weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
    FROM @test_dates
    CROSS APPLY
    (VALUES(DATEDIFF(DAY,6,start_date),DATEDIFF(DAY,6,end_date)))x(x,y);

    Cheers!

    That's very clever (especially since your low caffeine alarm is ringing :D) and, because of the integer math, is likely to be very fast.  Unfortunately, I believe there's a fly in the ointment.  Consider the following code that determines the day of the week for the given start and end dates to be used in an upcoming test...

     SELECT  DOW_19000101 = DATENAME(dw,'19000101')
    ,DOW_20991231 = DATENAME(dw,'20991231')
    ;

    That returns the following...

    The significance there is that, because the start date is a Monday and the end date is a Thursday, there MUST be an even number of days returned because neither end of the date range has split a weekend.

    If we run your good code using those two dates as follows (please correct me if I've somehow used it incorrectly)...

     SELECT weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 
    FROM (VALUES(DATEDIFF(DAY,6,'19000101'),DATEDIFF(DAY,6,'20991231')))x(x,y)
    ;

    ... it results in the following, which is an odd number... which means there's a flaw somewhere in the formula or, perhaps, it simply cannot handle dates prior to 1900-01-07 (which is what your use of the number "6" indicates)... which means we can't use this formula until there's a bit of proof as to what the problem is.  If it's the latter, I'm ok with saying that the start date must be after 1900-01-07 for use but we have to prove that's just a range limitation and not an actual problem.

     

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • He he, look what the cat dragged in!

    😎

    More than two years on and it's still interesting? Feels like eating last week's take out pizza.

     

  • Eirikur Eiriksson wrote:

    He he, look what the cat dragged in!

    😎

    More than two years on and it's still interesting? Feels like eating last week's take out pizza.

    Heh... I've done that.  It does take proper storage and preparation to prevent the screaming meemies though. 😀

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • We can make a simple change to Jacob's solution to fix the problem:

    DECLARE @test_dates TABLE (start_date DATE, end_date DATE);

    INSERT INTO @test_dates (start_date,end_date) VALUES
    ('20180301','20180430'),
    ('20201004','20201009'),
    ('20201004','20201010'),
    ('20201004','20201011'),
    ('20201004','20201012'),
    ('20201004','20201013'),
    ('20180301','20180429'),
    ('20180301','20180428'),
    ('20201004','20201014'),
    ('20201004','20201015'),
    ('20201004','20201016'),
    ('20200601','20200605'),
    ('20200601','20200606'),
    ('20201004','20201017'),
    ('20201004','20201018'),
    ('19000101','20991231');

    SELECT start_date,
    end_date,
    weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
    FROM @test_dates
    CROSS APPLY
    (VALUES(DATEDIFF(DAY,-1,start_date),DATEDIFF(DAY,-1,end_date)))x(x,y);

    However, this solution will not work for dates prior to 1900-01-01 unless we force the DATEDIFF calculation to use the DATE data type.  Using an integer value as the first parameter forces DATEDIFF to implicitly convert the values to DATETIME and the negative value will be 1 off the total.

    DECLARE @test_dates TABLE (start_date DATE, end_date DATE);

    INSERT INTO @test_dates (start_date,end_date) VALUES
    ('19000101','20991231'),
    ('17530101','20991231');

    SELECT start_date,
    end_date,
    weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
    FROM @test_dates
    CROSS APPLY
    (VALUES(DATEDIFF(DAY,-1,start_date),DATEDIFF(DAY,-1,end_date)))x(x,y);

    SELECT start_date,
    end_date,
    weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
    FROM @test_dates
    CROSS APPLY
    (VALUES(DATEDIFF(DAY,cast('1752-12-31' As date),start_date),DATEDIFF(DAY,cast('1752-12-31' As date),end_date)))x(x,y);

    This also leads to the problem of the 'first' week of the DATE data type - 0001-01-01 through 0001-01-06 cannot be used because we cannot define the Sunday prior to 0001-01-01 as a date.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff,

    Thanks for taking a look.

    The problem is indeed one of the range (as Jeffrey also pointed out while I typed this up). The math trick won't work once negative numbers get involved.

    If we push back the anchor Sunday to before the interval you specify for that test, then it returns 20870, as expected, although this can't always be done. I'm not sure I get the business use of figuring out weekends at the beginning of allowed date or datetime data ranges (like the year 0), but it's a fair technical concern for thoroughness' sake, of course :).

    I'm normally the paranoid sort that would point out those sorts of caveats when posting, but my diligence was flagging when I posted that; thanks for pointing it out!

    Could be addressed in a number of ways, ranging from just putting a caveat on its use to having the query automatically adjust negative numbers appropriately.

    I've been rolling this problem around in my head the last couple days, trying to see if I can come up with something even more concise, so I'll post some alternatives once I've given up on the brevity game.

    Cheers!

Viewing 15 posts - 31 through 45 (of 63 total)

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