Count the Number of Weekend Days between Two Dates

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    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)
    😎

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    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

  • ScottPletcher

    SSC Guru

    Points: 98558

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

  • Jeff Moden

    SSC Guru

    Points: 997132

    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)

Viewing 4 posts - 31 through 34 (of 34 total)

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