Reputation: 5873
simulating Excel networkdays in sql
I am sure somebody has worked out to simulate Excel networkdays function in other programming language. I'd appreciate if you could share. Thanks
Upvotes: 2
Views: 23542
Answers (7)
Reputation: 1
NETWORKDAYS for Australia
WITH PH_NET_WORKDAY AS
(
----BUILD UNDERLYING WORKDAY DATA TABLE
SELECT
DT,
STATE,
CASE
WHEN WORKDAY-PUBLIC_HOLIDAY <0
THEN 0
ELSE WORKDAY-PUBLIC_HOLIDAY
END AS WORKDAY
FROM
(
SELECT
DT,
STATE,
WORKDAY,
----PUBLIC HOLIDAY INFORMATION HERE
CASE
WHEN STATE = 'NSW'
THEN
CASE
WHEN DT IN (
'01-Oct-2018',
'25-Dec-2018',
'26-Dec-2018',
'01-Jan-2019',
'28-Jan-2019',
'19-Apr-2019',
'20-Apr-2019',
'21-Apr-2019',
'22-Apr-2019',
'25-Apr-2019',
'10-Jun-2019',
'07-Oct-2019',
'25-Dec-2019',
'26-Dec-2019'
)
THEN 1
ELSE 0
END
WHEN STATE = 'SA'
THEN
CASE
WHEN DT IN (
'01-Oct-2018',
'24-Dec-2018',
'25-Dec-2018',
'26-Dec-2018',
'31-Dec-2018',
'01-Jan-2019',
'28-Jan-2019',
'11-Mar-2019',
'19-Apr-2019',
'20-Apr-2019',
'22-Apr-2019',
'25-Apr-2019',
'10-Jun-2019',
'07-Oct-2019',
'24-Dec-2019',
'25-Dec-2019',
'26-Dec-2019'
)
THEN 1
ELSE 0
END
WHEN STATE = 'QLD'
THEN
CASE
WHEN DT IN (
'01-Oct-2018',
'25-Dec-2018',
'26-Dec-2018',
'01-Jan-2019',
'28-Jan-2019',
'19-Apr-2019',
'20-Apr-2019',
'21-Apr-2019',
'22-Apr-2019',
'25-Apr-2019',
'06-May-2019',
'07-Oct-2019',
'25-Dec-2019',
'26-Dec-2019'
)
THEN 1
ELSE 0
END
WHEN STATE = 'VIC'
THEN
CASE
WHEN DT IN (
'28-Sep-2018',
'06-Nov-2018',
'25-Dec-2018',
'26-Dec-2018',
'01-Jan-2019',
'28-Jan-2019',
'11-Mar-2019',
'19-Apr-2019',
'20-Apr-2019',
'21-Apr-2019',
'22-Apr-2019',
'25-Apr-2019',
'10-Jun-2019',
'05-Nov-2019',
'25-Dec-2019',
'26-Dec-2019'
)
THEN 1
ELSE 0
END
WHEN STATE = 'TAS'
THEN
CASE
WHEN DT IN (
'25-Dec-2018',
'26-Dec-2018',
'01-Jan-2019',
'28-Jan-2019',
'11-Mar-2019',
'19-Apr-2019',
'22-Apr-2019',
'23-Apr-2019',
'25-Apr-2019',
'10-Jun-2019',
'25-Dec-2019',
'26-Dec-2019'
)
THEN 1
ELSE 0
END
ELSE 0
END AS PUBLIC_HOLIDAY
FROM
(
SELECT
DT.*,
ST.*
FROM
(
SELECT
TRUNC (TO_DATE('01-JAN-2021','DD-MON-YYYY') - ROWNUM) AS DT,
TRIM(TO_CHAR( TRUNC (TO_DATE('01-JAN-2021','DD-MON-YYYY') - ROWNUM) , 'DAY')) AS WEEK_DAY,
CASE
WHEN TRIM(TO_CHAR( TRUNC (TO_DATE('01-JAN-2021','DD-MON-YYYY') - ROWNUM) , 'DAY')) = 'SATURDAY'
THEN 0
WHEN TRIM(TO_CHAR( TRUNC (TO_DATE('01-JAN-2021','DD-MON-YYYY') - ROWNUM) , 'DAY')) = 'SUNDAY'
THEN 0
----BUILD STATE PUBLIC HOLIDAY DATE SET HERE, WE CAN EXCLUDE PUBLIC HOLIDAYS
ELSE 1
END AS WORKDAY
FROM DUAL CONNECT BY ROWNUM < (365.25*8+1)
) DT
,
(
SELECT 'NSW' AS STATE FROM DUAL
UNION
SELECT 'QLD' AS STATE FROM DUAL
UNION
SELECT 'SA' AS STATE FROM DUAL
UNION
SELECT 'ACT' AS STATE FROM DUAL
UNION
SELECT 'VIC' AS STATE FROM DUAL
UNION
SELECT 'ACT' AS STATE FROM DUAL
) ST
)
)
),
----A SAMPLE DATA SET FOR SAME DATES BETWEEN DIFFERENT STATE TO TEST PUBLIC HOLIDAY DIFFERENCES
SAMPLE_DATA AS
(
SELECT
'01-FEB-2019' AS D1,
'11-FEB-2019' AS D2,
'NSW' AS STATE
FROM DUAL
UNION
SELECT
'01-FEB-2019' AS D1,
'11-FEB-2019' AS D2,
'SA' AS STATE
FROM DUAL
UNION
SELECT
'19-APR-2019' AS D1,
'26-APR-2019' AS D2,
'NSW' AS STATE
FROM DUAL
)
----SELECT WORKDAYS FROM PH TABLE AND INSERT INTO SAPLE TABLE
SELECT
SAMPLE_DATA.*,
(
SELECT SUM(WORKDAY)
FROM PH_NET_WORKDAY
WHERE
STATE = SAMPLE_DATA.STATE
AND DT>=SAMPLE_DATA.D1
AND DT<=SAMPLE_DATA.D2
)
AS WORKDAYS
FROM SAMPLE_DATA
Upvotes: 0
Reputation: 1
I have been looking for this capability for quite some time, so went ahead and just created it on my own.
Usage: This is a function you can create in SQL. You can easily modify this to work outside a function if needed, but I prefer functions to take care of these types of calculations.
I added quite a few comments in case anyone was wondering how it worked. What this does is take two inputs:
- @startDate - the beginning date you want to add workday to.
- @addDays - the whole number of days you want to add to the @startDate.
Calculation process: It loops repeatedly until the # of working days has been reached. For example, if you enter 365 days, it will cycle around 500 times before it's able to predict the # of working days to add. I've added @weekendCount in case anyone needs to know the # of weekend days excluded before reaching the final end date.
Once completed, the integer @recCounter essentially is the # of days that must be added to the @startDate before the number of working days is reached. I am sure someone can write this better than I, or perhaps someone can make use of this. Hope this helps! :)
CREATE FUNCTION [dbo].[addNetworkDays](@startDate AS DATETIME, @addDays AS Int)
RETURNS DATETIME
AS
BEGIN
DECLARE @recCounter Int
SET @recCounter = 0
DECLARE @weekendCount Int
SET @weekendCount = 0
DECLARE @workdayCount Int
SET @workdayCount = 0
DECLARE @newDate DateTime
WHILE @addDays > @workdayCount
BEGIN
-- Add another day to the start date
SET @recCounter = @recCounter + 1
-- Cumuluate the weekend vs. workday counter, based on the day of the week. This loop will repeat until @workdayCount has reached the @addDays.
-- Note that @weekendCount is not used in any capacity, can be used if you need to know how many weekend days there are.
IF DATEPART(dw, DATEADD(d, @recCounter, @startDate)) IN (1, 7) SET @weekendCount = @weekendCount + 1
IF DATEPART(dw, DATEADD(d, @recCounter, @startDate)) IN (2, 3, 4, 5, 6) SET @workdayCount = @workdayCount + 1
END
-- At this point, the script has completed the cycle, stopping when the detected # of workdays has reached the count of days the user specified.
-- Calculate the new date, based on the # of cycles *days* detected above.
SET @newDate = DATEADD(d, @recCounter, @startDate)
-- If the new/adjusted date falls on a Saturday or Sunday, add additional days to compensate.
IF DATEPART(dw, @newDate) = 1 SET @newDate = DATEADD(d, 1, @newDate)
IF DATEPART(dw, @newDate) = 7 SET @newDate = DATEADD(d, 1, @newDate)
RETURN CAST(@newDate AS DATETIME)
Upvotes: 0
Reputation: 207
Hope this helps someone out there but this works for me. Requires you to create a dbo.FederalHolidays table (which can be easily populated with online date sources). Cleanest way I could come up with that is easily scalable.
--Simulate Excel Formula =NETWORKDAYS() excludes weekend days and federal holidays. Requires a Federal Holiday Table, easy to create.
DECLARE @d1 date, @d2 date
SET @d1 = '4/12/2019'
SET @d2 = '4/23/2019'
SELECT
DATEDIFF(dd,@d1,@d2) +1 --First calculate regular date difference +1 to count the start date; does not exclude weekends/holidays.
- (SELECT COUNT(*) --Calculate number of holidays between the date range and subtract it from DATEDIFF().
FROM [dbo].[FederalHolidays]
WHERE DATE BETWEEN @d1 AND @d2)
- (SELECT (DATEDIFF(wk, @d1, @d2) * 2) --Calculate number of weekend days between the date range and subtract it from DATEDIFF().
+(CASE WHEN DATENAME(dw, @d1) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, @d2) = 'Saturday' THEN 1 ELSE 0 END)) as NetWorkDays
Upvotes: 3
Reputation: 3511
Perhaps this will also help, I created a formula (in Excel) that will simulate the NETWORKDAYS function:
= 1 + ( ( B1 - A1) * 5 - ( WEEKDAY(A1) - WEEKDAY(B1) ) * 2 ) / 7 + IF(A1<=B1,IF(WEEKDAY(B1)=7,-1,0) + IF(WEEKDAY(A1)=1,-1,0), IF(WEEKDAY(B1)<>1,-1,0) + IF(WEEKDAY(A1)<>7,-1,0) )
NOTE: WEEKDAY() has Sunday as 0 to Saturday as 6
Upvotes: 0
Reputation: 17451
For what it's worth, I created the following function for mysql that assumes Mon-Fri are business days:
DROP FUNCTION IF EXISTS BusinessDays;
DELIMITER //
CREATE FUNCTION BusinessDays (startDate DATE, endDate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE startWeekDay INT;
DECLARE allDays INT;
DECLARE fullWeekCount INT;
DECLARE remainderDays INT;
DECLARE maxPossibleRemainderWeekendDays INT;
DECLARE soloSundays INT;
DECLARE totalBusinessDays INT;
SET startWeekDay = WEEKDAY(startDate);
SET allDays = ABS(DATEDIFF(endDate, startDate)) + 1;
SET fullWeekCount = FLOOR(allDays/7);
SET remainderDays = allDays - (fullWeekCount * 7);
SET maxPossibleRemainderWeekendDays = ROUND(2*(startWeekDay+remainderDays-6)/(ABS(2*(startWeekDay+remainderDays-6))+1))+1;
SET soloSundays = ROUND(2*(startWeekDay-6)/(ABS(2*(startWeekDay-6))+1))+1;
SET totalBusinessDays = allDays - (fullWeekCount * 2) - maxPossibleRemainderWeekendDays + soloSundays;
RETURN totalBusinessDays;
END //
DELIMITER ;
Upvotes: 0
Reputation: 367
The idea is to calculate the weekdays between the start of each date's week then applying various offsets.
- Find the number of days between the Saturday before each date
- Divide by 7 and multiply by 5 to get the number of weekdays
- Offset the total for whether the start date is after the end date
- Offset again for whether the start is after the end and the start is a Saturday
- Again for whether the start is after and the end is Sunday
- Again for whether the start is not after and the start is a Sunday
- Again for whether the start is not after and the end is a Saturday
Add some random dates into a table.
declare @t table ([start] datetime, [end] datetime)
insert into @t values ('2088-01-14 11:56:23','2011-11-10 03:34:09')
insert into @t values ('2024-09-24 10:14:29','2087-09-16 15:52:06')
Then calcuate NETWORKDAYS for those dates.
select [start],[end]
,((datediff(day,0,[end])-datepart(dw,[end]))-(datediff(day,0,[start])-datepart(dw,[start])))/7*5 --[weekdays]
+ datepart(dw,[end]) - datepart(dw,[start]) --[weekday diff]
+ case when datediff(day,0,[start]) > datediff(day,0,[end]) then -1 else 1 end --[start after]
+ case when datediff(day,0,[start]) > datediff(day,0,[end]) and datepart(dw,[start]) = 7 then 1 else 0 end --[start after and start saturday]
+ case when datediff(day,0,[start]) > datediff(day,0,[end]) and datepart(dw,[end]) = 1 then 1 else 0 end --[start after and end sunday]
+ case when datediff(day,0,[start]) <= datediff(day,0,[end]) and datepart(dw,[start]) = 1 then -1 else 0 end --[start not after and start sunday]
+ case when datediff(day,0,[start]) <= datediff(day,0,[end]) and datepart(dw,[end]) = 7 then -1 else 0 end --[start not after and end saturday]
as [networkdays]
from @t
Upvotes: 3
Reputation: 432521
A SQL solution as per SO question "Equivalent of Excel’s NETWORKDAYS function with Jet ADO"
Upvotes: 2
Related Questions
- Simulating NETWORKDAYS returns wrong business days
- Networkdays Function
- Function NETWORKDAYS.INTL
- Equivalent of Excel’s NETWORKDAYS function with Jet ADO
- Calculating Network Days Difference in VBA
- Networkdays Function for range of cells in VBA
- Networkdays in VBA for a range of dates
- Excel VBA using NETWORKDAYS
- Microsoft Excel 2010: =NETWORKDAYS()
- Is there a .NET method equivalent to NETWORKDAYS in Excel?