Count the Number of Weekend Days between Two Dates

• Eirikur Eiriksson

SSC Guru

Points: 182509

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

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;GOSET NOCOUNT ON;GOCREATE 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 TABLEWITH SCHEMABINDINGASRETURNWITH 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_DAYSFROM  BASE_CALC BC;`

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

`USE TEEST;GOSET 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_DAYSFROM  BASE_CALC BC;`

Output
`FROM_DATE TO_DATE  WEEK_END_DAYS---------- ---------- -------------2018-03-01 2018-04-30 182018-03-01 2018-04-29 182018-03-01 2018-04-28 172018-03-01 2018-03-04 22018-03-01 2018-03-03 12018-02-26 2018-03-02 02018-08-26 2018-09-02 32018-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_daysFROM ( 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!"