October 3, 2019 at 2:48 pm
does anyone have a good solution for getting the number of working days between 2 dates
I've inherited a horrible function which uses a loop to count the days between 2 dates and I want to get rid of the loop (this is used in many reports and is usually put into a SUM() inside a group by)
here is the evil code
ALTER FUNCTION [dbo].[f_get_working_days]
(
@datetime_from DATETIME,
@datetime_to DATETIME,
@time_from_deadline DECIMAL(5, 3),
@time_to_deadline DECIMAL(5, 3)
)
RETURNS INT
AS
BEGIN
-- returns number of working days between 2 dates
-- if both dates are today, returns 0 not 1
-- if time part of datetime_from is after time_from_deadline, sets date_from to next day
-- if time part of datetime_to is before time_to_deadline, sets date_to to previous day
-- (leave time deadlines as zero to ignore)
-- mainly used for calculating SLA or KPI figures
-- use decimal time deadlines (to allow for 12:30 put 12.5 for example)
DECLARE @date_this DATETIME;
DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;
DECLARE @days INT;
-- update date from
IF @time_from_deadline > 0
AND DATEPART(hh, @datetime_from) + 1.0 * DATEPART(mi, @datetime_from) / 60 >= @time_from_deadline
-- time part of datetime_from is after time_deadline
-- effectively set date_from to next day
SELECT @date_from = CONVERT(DATETIME, CONVERT(VARCHAR(10), @datetime_from + 1, 103), 103);
ELSE
-- set date_from to this date
SELECT @date_from = CONVERT(DATETIME, CONVERT(VARCHAR(10), @datetime_from, 103), 103);
-- update date to
IF @time_to_deadline > 0
AND DATEPART(hh, @datetime_to) + 1.0 * DATEPART(mi, @datetime_to) / 60 < @time_to_deadline
-- time part of datetime_to is before time_deadline
-- effectively set date_to to previous day
SELECT @date_to = CONVERT(DATETIME, CONVERT(VARCHAR(10), @datetime_to - 1, 103), 103);
ELSE
-- set date_to to this date
SELECT @date_to = CONVERT(DATETIME, CONVERT(VARCHAR(10), @datetime_to, 103), 103);
-- initialise count
SELECT @date_this = @date_from,
@days = 0;
-- determine if date_to is after or before date_from
-- usually should be after, but just in case
IF @date_from < @date_to
BEGIN
-- date_to is after date_from, therefore count days forwards
-- loop through dates
WHILE @date_this < @date_to
BEGIN
IF DATEPART(dw, @date_this) < 6
AND NOT EXISTS
(
SELECT *
FROM holiday WITH (NOLOCK)
WHERE date_holiday = @date_this
)
-- this is a working day (not a saturday, sunday or bank holiday)
SELECT @days = @days + 1;
-- next day
SELECT @date_this = @date_this + 1;
END; -- loop
END;
ELSE
BEGIN
-- date_to is before date_from, set days to zero rather than negative
SELECT @days = 0;
END;
-- return number of days
RETURN @days;
END;
basically Saturdays and sundays don't count, neither do bank/national holidays
MVDBA
October 3, 2019 at 2:54 pm
October 3, 2019 at 4:54 pm
You would probably want to create a calendar table when you also want to exclude bank and national holidays since those can vary by country, company and whatever else. These links should give you a basic idea around calendar tables:
Bones of SQL - The Calendar Table
Sue
October 3, 2019 at 5:16 pm
A calendar table is key and Bobs solution works well to build one
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 3, 2019 at 5:32 pm
That function looks like a horror movie and it's not even Halloween yet. The function below, ifn_workdays, is what you need. It does not handle holidays. Here's an example and the function.
DECLARE @holidays TABLE (HDate DATE, Holiday VARCHAR(20), WKDay AS DATENAME(WEEKDAY,HDate));
INSERT @holidays (Hdate,Holiday)
VALUES ('20191224','Christmas Eve'),('20191225','Christmas Day'),('20191231','New Years Eve')
DECLARE @startdate DATE = '20191201', @enddate DATE = '20191231';
WITH hday(total) AS
(
SELECT COUNT(*)
FROM @holidays AS h
WHERE h.HDate BETWEEN @startdate AND @enddate
AND h.WKDay NOT IN ('Saturday','Sunday')
)
SELECT workdays = wd.workdays-h.total
FROM dbo.ifn_WorkDays(@startdate,@enddate) AS wd
CROSS JOIN hday AS h;
The function:
CREATE FUNCTION dbo.ifn_WorkDays
/***************************************************************************************
Purpose:
1. Given any valid start date and end date, this function will calculate and return
the number of workdays (Mon - Fri).
2. Given only a valid start date (end date has DEFAULT in it), this function will
return a 1 if the start date is a weekday and a 0 if not a weekday.
Notes:
1. Holidays are NOT considered.
2. Because of the way SQL Server calculates weeks and named days of the week, no
special consideration for the value of DATEFIRST is given. In other words, it
doesn't matter what DATEFIRST is set to for this function.
3. If the input dates are in the incorrect order, they will be reversed prior to any
calculations.
4. Only whole days are considered. Times are NOT used.
5. The number of workdays INCLUDES both dates
6. Inputs may be literal representations of dates, datetime datatypes, numbers that
represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
be implicitly converted to or already is a datetime datatype.
7. Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts
only whole weekends in any given date range.
8. This UDF does NOT create a tally table or sequence table to operate. Not only is
it set based, it is truly "tableless".
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
Rev 03 - 01/03/2017 - Luis Cazares - Change the function into an iTVF. Keep the functionality
Rev 04 - 06/08/2018 - Alan Burstein - 1. Moved NULL parameter filering from subquery "x" to the
WHERE clause to remove a filter from the execution plan.
2. Updated function to return a NULL if either @startDate
OR @enddate are NULL.
3. Added SCHEMABINDING
*/
(
@startDate datetime,
@endDate datetime
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT workDays =
-- If @startDate or @endDate are NULL then rerturn a NULL
CASE WHEN DATEDIFF(dd, @startDate, @endDate) IS NOT NULL THEN
(DATEDIFF(dd, startDate, endDate) + 1) --Start with total days including weekends
-(DATEDIFF(wk, startDate, endDate) * 2) --Subtact 2 days for each full weekend
-- Subtract 1 when startDate is Sunday and Substract 1 when endDate is Sunday:
-(CASE WHEN DATENAME(dw, startDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, endDate) = 'Saturday' THEN 1 ELSE 0 END)
END
FROM
( -- if @endDate is earlier that @startDate then flip them
SELECT StartDate = DATEADD(dd, DATEDIFF(dd,0,MIN(adate)), 0),
EndDate = DATEADD(dd, DATEDIFF(dd,0,MAX(adate)), 0)
FROM (VALUES(@startDate),(@endDate)) x(adate))y;
GO
You would probably want to create a calendar table when you also want to exclude bank and national holidays since those can vary by country, company and whatever else. These links should give you a basic idea around calendar tables:
Bones of SQL - The Calendar Table
Sue
Set-based is always the way to go unless you don't need a set at all. 😉
-- Itzik Ben-Gan 2001
October 3, 2019 at 5:37 pm
Sue_H wrote:You would probably want to create a calendar table when you also want to exclude bank and national holidays since those can vary by country, company and whatever else. These links should give you a basic idea around calendar tables:
Bones of SQL - The Calendar Table
Sue
Set-based is always the way to go unless you don't need a set at all for calculating weekdays 😉
The requirements are to also look at bank holidays and national holidays, which vary by company and country. So limiting things to weekdays only does not meet the requirements. And yes set based in the way to go and a calendar table does make it set based if you know how to use one. But especially nice to use when it can meet the requirements 🙂
Sue
October 3, 2019 at 6:52 pm
Alan Burstein wrote:Sue_H wrote:You would probably want to create a calendar table when you also want to exclude bank and national holidays since those can vary by country, company and whatever else. These links should give you a basic idea around calendar tables:
Bones of SQL - The Calendar Table
Sue
Set-based is always the way to go unless you don't need a set at all for calculating weekdays 😉
The requirements are to also look at bank holidays and national holidays, which vary by company and country. So limiting things to weekdays only does not meet the requirements.
Sue
Which is why I included a sample calendar table. The function will count the number of days between the dates excluding weekends; aliased as wd.workdays. The CTE hday gets the number of holidays which also fall into the date range and occur on a weekday; this is aliased as hday.total. The final calculation is: workdays = wd.workdays-h.total
DECLARE @holidays TABLE (HDate DATE, Holiday VARCHAR(20), WKDay AS DATENAME(WEEKDAY,HDate));
INSERT @holidays (Hdate,Holiday)
VALUES ('20191224','Christmas Eve'),('20191225','Christmas Day'),('20191231','New Years Eve')
DECLARE @startdate DATE = '20191201', @enddate DATE = '20191231';
WITH hday(total) AS
(
SELECT COUNT(*)
FROM @holidays AS h
WHERE h.HDate BETWEEN @startdate AND @enddate
AND h.WKDay NOT IN ('Saturday','Sunday')
)
SELECT workdays = wd.workdays-h.total
FROM dbo.ifn_WorkDays(@startdate,@enddate) AS wd
CROSS JOIN hday AS h;
-- Itzik Ben-Gan 2001
October 3, 2019 at 9:22 pm
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).
The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_nbr INTEGER NOT NULL,
...);
INSERT INTO Calendar
VALUES ('2007-04-05', 42);
('2007-04-06', 43); -- Good Friday
('2007-04-07', 43);
('2007-04-08', 43); -- Easter Sunday
('2007-04-09', 44);
('2007-04-10', 45); -- Tuesday, back to work
To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';
This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. Your problem is that you still think in prodcedural code, but SQL is a declarative language.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 4, 2019 at 8:02 am
so - hold on - are you guys saying that I should put every Saturday and sunday into my holidays table? that kinda makes sense.....
all I have to do is a datediff and then subtract count(*) and i'm all good
and heck no.... I don't think in procedural code, i'm 100% set based declarative... it's the prior developer who wrote this masterclass of stupidity
MVDBA
October 5, 2019 at 3:58 pm
so - hold on - are you guys saying that I should put every Saturday and sunday into my holidays table? that kinda makes sense.....
all I have to do is a datediff and then subtract count(*) and i'm all good
and heck no.... I don't think in procedural code, i'm 100% set based declarative... it's the prior developer who wrote this masterclass of stupidity
I don't think that is the recommendation - you should have a single calendar table with all dates including holidays. Barring that - you can have a calendar table and a holiday table, but then you need to join and filter by both tables.
If you have a Calendar table - you can join that to your Holiday table based on the date so you can determine weekends and holidays, then either filter out those days from the results - or filter them out of any counts.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2019 at 4:01 pm
so - hold on - are you guys saying that I should put every Saturday and sunday into my holidays table? that kinda makes sense.....
all I have to do is a datediff and then subtract count(*) and i'm all good
and heck no.... I don't think in procedural code, i'm 100% set based declarative... it's the prior developer who wrote this masterclass of stupidity
I don't think that is the recommendation - you should have a single calendar table with all dates including holidays. Barring that - you can have a calendar table and a holiday table, but then you need to join and filter by both tables.
If you have a Calendar table - you can join that to your Holiday table based on the date so you can determine weekends and holidays, then either filter out those days from the results - or filter them out of any counts.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2019 at 4:02 pm
so - hold on - are you guys saying that I should put every Saturday and sunday into my holidays table? that kinda makes sense.....
all I have to do is a datediff and then subtract count(*) and i'm all good
and heck no.... I don't think in procedural code, i'm 100% set based declarative... it's the prior developer who wrote this masterclass of stupidity
I don't think that is the recommendation - you should have a single calendar table with all dates including holidays. Barring that - you can have a calendar table and a holiday table, but then you need to join and filter by both tables.
If you have a Calendar table - you can join that to your Holiday table based on the date so you can determine weekends and holidays, then either filter out those days from the results - or filter them out of any counts.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2019 at 6:18 pm
FWIW, I'd try to stick with one table, and use flags in other columns for items like holidays, workday, etc. I'd make this more like a dimension table in a DW so that all clients can use it for determine what dates are useful/valid.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply