# Calculating Work Days

• Awesome. Thanks for the feedback, JeeTee. It's not often that a decade old thread gets feedback like this.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Just a small update.

`--===== If the function already exists, drop it`

` IF OBJECT_ID(N'[dbo].[ifn_WorkDays]') IS NOT NULL`

` DROP FUNCTION [dbo].[ifn_WorkDays]`

`GO`

`GO`

` 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`

`*/`

`(`

` @StartDate datetime,`

` @EndDate datetime`

`)`

`RETURNS TABLE`

`AS`

`RETURN`

` SELECT --Start with total number of days including weekends`

` (DATEDIFF(dd,StartDate,EndDate)+1)`

` --Subtact 2 days for each full weekend`

` -(DATEDIFF(wk,StartDate,EndDate)*2)`

` --If StartDate is a Sunday, Subtract 1`

` -(CASE WHEN DATENAME(dw,StartDate) = 'Sunday'`

` THEN 1`

` ELSE 0`

` END)`

` --If EndDate is a Saturday, Subtract 1`

` -(CASE WHEN DATENAME(dw,EndDate) = 'Saturday'`

` THEN 1`

` ELSE 0`

` END) AS WorkDays`

` FROM (SELECT DATEADD(dd,DATEDIFF(dd,0,MIN(adate)),0) AS StartDate, DATEADD(dd,DATEDIFF(dd,0,MAX(adate)),0) AS EndDate`

` FROM (VALUES(@StartDate),(@EndDate))x(adate)`

` WHERE @StartDate IS NOT NULL)y`

`GO`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Spot on, Luis! Thanks for the update.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I stumbled across this while looking to create my own and would like to propose a couple changes:
First, I have noticed lately moving filters from a WHERE clause to a WHEN clause often results in nice performance gains. Also, this:
`SELECT * FROM dbo.ifn_WorkDays(getdate(),NULL)`
Returns a 1. To fix that I changed WHERE @StartDate IS NOT NULL in the subquery to WHEN DATEDIFF(dd, @startDate, @endDate) IS NOT NULL in my CASE statement.... and some schemabinding. Here's the proposed updated version:
`--===== If the function already exists, drop itIF OBJECT_ID(N'dbo.ifn_WorkDays') IS NOT NULL DROP FUNCTION dbo.ifn_WorkDaysGO 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 RETURNSELECT 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) ENDFROM ( -- 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`
Moving the filter from the WHERE clause to the CASE statement changes the execution plan like so:

Here's a quick performance test I cooked up (dbo.ifn_WorkDays is the original, dbo.ifn_WorkDaysV2 is the updated version):
`-- 1 Million Row Test HarnessSET NOCOUNT ON;IF OBJECT_ID('tempdb..#date') IS NOT NULL DROP TABLE #dateSELECT TOP (1000000) d1 = DATEADD(dd, checksum(newid())%2000-2000, getdate()), d2 = DATEADD(dd, checksum(newid())%2000-2000, getdate())INTO #dateFROM sys.all_columns a, sys.all_columns b;GO`

`-- Start FreshDBCC FREEPROCCACHE  with no_infomsgs;DBCC DROPCLEANBUFFERS with no_infomsgs;`

`IF OBJECT_ID('tempdb..#stats') IS NOT NULL DROP TABLE #stats;CREATE TABLE #stats (v tinyint, tm int);GO`

`PRINT char(13)+char(10)+'Print dbo.ifn_WorkDays'+char(13)+char(10);GO DECLARE @st datetime = getdate(), @x int; SELECT @x = w.WorkDays FROM #date d CROSS APPLY dbo.ifn_WorkDays(d.d1, d.d2) w OPTION (MAXDOP 1)`

` INSERT #stats VALUES (1, DATEDIFF(MS, @st, getdate()));GO 5`

`PRINT char(13)+char(10)+'Print dbo.ifn_WorkDaysV2'+char(13)+char(10);GO DECLARE @st datetime = getdate(), @x int; SELECT @x = w.WorkDays FROM #date d CROSS APPLY dbo.ifn_WorkDaysV2(d.d1, d.d2) w OPTION (MAXDOP 1)`

` INSERT #stats VALUES (2, DATEDIFF(MS, @st, getdate()));GO 5`

`-- raw stats:SELECT [version] = CASE v WHEN 1 THEN 'old' ELSE 'new' END, [time]  = tmFROM #stats;`

`-- total average:SELECT [version] = CASE v WHEN 1 THEN 'old' ELSE 'new' END, average = AVG(i.tm)FROM #stats i GROUP BY i.v;`
Note that I tested this on my laptop and used MAXDOP 1 because I was getting a parallel plan that was not improving the performance any.

Results:

version time
------- -----------
old  1080
old  1070
old  1076
old  1074
old  1113
new  940
new  900
new  943
new  950
new  940

version average
------- -----------
old  1082
new  934

That's ~15% performance improvement.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Couldn't help it as when I read through the article and the whole discussion, I saw some obvious opportunities for improvement.
😎

Two pointers, an implicit conversion to nvarchar as the datename function returns nvarchar and datediff to 0 is more than five times more expensive than comparing the input values in a case statement.

Improved function

`USE TEEST;`
`GO`
`SET NOCOUNT ON`
`GO`
`CREATE OR ALTER FUNCTION dbo.ITVFN_CALC_WORKING_DAYS`
`---------------------------------------------------------------------`
`-- Calculate the number of working days between two input dates.`
`-- This function is a rewrite of functions posted in Jeff Moden's `
`-- article, Calculate Working Days.`
`-- https://www.sqlservercentral.com/Forums/153606/Calculating-Work-Days`
`---------------------------------------------------------------------`
`(`
`  @STARTDATE DATETIME`
` ,@ENDDATE DATETIME`
`)`
`RETURNS TABLE`
`WITH SCHEMABINDING`
`AS`
`RETURN`
`---------------------------------------------------------------------`
`-- Set the date order, just in case`
`---------------------------------------------------------------------`
`WITH DATE_ORDER AS `
`(`
`  SELECT `
`   CASE `
`    WHEN @STARTDATE <= @ENDDATE THEN @STARTDATE`
`    ELSE @ENDDATE`
`   END AS START_DATE`
`   ,CASE `
`    WHEN @ENDDATE >= @STARTDATE THEN @ENDDATE`
`    ELSE @STARTDATE`
`   END AS END_DATE`
`)`
`,DAY_DIFFERENCE AS `
`(`
`  SELECT `
`   DATEDIFF(DAY,DO.START_DATE,DO.END_DATE) + 1 AS DD_COUNT`
`   ,DO.START_DATE`
`   ,DO.END_DATE`
`   ,CASE `
`    DATENAME(WEEKDAY, DO.START_DATE)    `
`    WHEN N'Sunday' THEN -1 `
`    ELSE    0`
`   END AS START_WD`
`   ,CASE `
`    DATENAME(WEEKDAY, DO.END_DATE)     `
`    WHEN N'Saturday' THEN -1 `
`    ELSE    0 `
`   END AS END_WD`
`  FROM DATE_ORDER DO `
`)`
`SELECT `
`  DD.DD_COUNT + (( -(DATEDIFF(WEEK,DD.START_DATE,DD.END_DATE)) * 2) + DD.START_WD + DD.END_WD) AS WORK_DAYS`
`FROM DAY_DIFFERENCE DD;`

A test harness, configured for 1,000,000 rows

`USE TEEST;`
`GO`
`SET NOCOUNT ON;`
`DECLARE @SAMPLE_SIZE  BIGINT  = 1000000;`
`DECLARE @FIRST_DATE  DATETIME  = '20160101';`
`DECLARE @SD_RANGE   INT   = 3653;`
`DECLARE @ED_RANGE   INT   = 120;`
`DECLARE @INT_BUCKET  INT   = 0;`
`DECLARE @BIGINT_BUCKET BIGINT  = 0;`
`DECLARE @DT_BUCKET  DATETIME  = 0;`
`DECLARE @timer TABLE `
`(`
` T_TS DATETIME2(7)  NOT NULL DEFAULT (SYSDATETIME())`
` ,T_TXT VARCHAR(50)  NOT NULL `
`);`
`--/*`
`IF OBJECT_ID(N'dbo.TBL_TEST_WORKING_DAYS') IS NOT NULL DROP TABLE dbo.TBL_TEST_WORKING_DAYS;`
`CREATE TABLE dbo.TBL_TEST_WORKING_DAYS`
`(`
`  TWD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_WORKING_DAYS_TWD_ID PRIMARY KEY CLUSTERED `
` ,TWD_START_DATE DATETIME NOT NULL `
` ,TWD_END_DATE  DATETIME NOT NULL`
`);`
`;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))`
`, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N `
`     FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)`
`,START_DAYS AS `
`(`
`  SELECT `
`   DATEADD(DAY,(ABS(CHECKSUM(NEWID())) % @SD_RANGE),@FIRST_DATE) AS START_DATE`
`  FROM NUMS NM`
`)`
`INSERT INTO dbo.TBL_TEST_WORKING_DAYS WITH (TABLOCK) (TWD_START_DATE,TWD_END_DATE)`
`SELECT`
`  SD.START_DATE`
` ,DATEADD(DAY,(ABS(CHECKSUM(NEWID())) % @ED_RANGE),SD.START_DATE) AS END_DATE`
`FROM START_DAYS SD;`
`-- */`
`INSERT INTO @timer(T_TXT) VALUES('DRY RUN')`
`SELECT `
`  @DT_BUCKET = T.TWD_START_DATE`
` ,@DT_BUCKET = T.TWD_END_DATE`
`FROM dbo.TBL_TEST_WORKING_DAYS T;`
`INSERT INTO @timer(T_TXT) VALUES('DRY RUN')`
`INSERT INTO @timer(T_TXT) VALUES('ITVFN_CALC_WORKING_DAYS')`
`SELECT `
`  @INT_BUCKET = CW.WORK_DAYS`
`FROM dbo.TBL_TEST_WORKING_DAYS T`
`CROSS APPLY dbo.ITVFN_CALC_WORKING_DAYS(T.TWD_START_DATE,T.TWD_END_DATE) CW`
`OPTION (MAXDOP 1);`
`INSERT INTO @timer(T_TXT) VALUES('ITVFN_CALC_WORKING_DAYS')`
`INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays Alan')`
`SELECT `
`  @INT_BUCKET = CW.workDays`
`FROM dbo.TBL_TEST_WORKING_DAYS T`
`CROSS APPLY dbo.ifn_WorkDays(T.TWD_START_DATE,T.TWD_END_DATE) CW`
`OPTION (MAXDOP 1);`
`INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays Alan')`
`INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays_Old')`
`SELECT `
`  @INT_BUCKET = CW.workDays`
`FROM dbo.TBL_TEST_WORKING_DAYS T`
`CROSS APPLY dbo.ifn_WorkDays_Old(T.TWD_START_DATE,T.TWD_END_DATE) CW`
`OPTION (MAXDOP 1);`
`INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays_Old')`
`-------------------------------------------------------------------------------`
`-- CALCULATE AND DISPLAY THE RESULTS`
`-------------------------------------------------------------------------------`
`SELECT `
` T.T_TXT`
` ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION`
`FROM @timer T `
`GROUP BY T.T_TXT`
`ORDER BY DURATION;`

Results
`T_TXT                  DURATIONDRY RUN                  138479ITVFN_CALC_WORKING_DAYS  585864ifn_WorkDays Alan       1077415ifn_WorkDays_Old        1328022`

• Heh... this is a very old article... I don't believe I even knew what an implicit conversion was back then. 😀

Anyway, thanks for the science, Eirikur.  Being a bit of a speed phreak myself, I always enjoy seeing performance improvments.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden - Wednesday, June 13, 2018 8:09 PM

Heh... this is a very old article... I don't believe I even knew what an implicit conversion was back then. 😀

Anyway, thanks for the science, Eirikur.  Being a bit of a speed phreak myself, I always enjoy seeing performance improvments.

You know how it is, seeing an opportunity like this, one just can't help it.
😎

Viewing 7 posts - 151 through 156 (of 156 total)