Count the Number of Weekend Days between Two Dates
- Jeff Moden - Thursday, August 23, 2018 10:47 PM
+(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 ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Friday, August 24, 2018 8:23 AM
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.
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 calc2SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- 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 calc2There 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_SundaysGrayB
-
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.
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.
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
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 62 total)
You must be logged in to reply to this topic. Login to reply