Sql Formula that calculates the workweek and Excludes weekends

Steven_99
Steven_99 used Ask the Experts™
on
Need Sql Formula that calculates the difference between two dates but excludes weekends and Holidays  much like the Workweek function in Excel.

This is what I've tried so far with no luck.

update tblCallBack_Data_m
Set LagTime = (DateDiff(ww,Inbox_Arrival,date_Closed)*2)-((DateDiff(d,Inbox_Arrival,date_Closed)))
Where Closed = 1
update tblCallBack_Data_m
Set LagTime = (DateDiff(ww,Inbox_Arrival,GetDate())*2)-((DateDiff(d,Inbox_Arrival,GetDate())))
Where Closed = 0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
SQL has no ideas of holidays. You will need a table of these!

Commented:
This takes care of weekends

SELECT     (DATEDIFF(d, '11 / 1 / 2002', GETDATE()) - DATEDIFF(d, ' 11 / 1 / 2002 ', GETDATE()) * 2 / 7) - (DATEPART(dw, GETDATE()) - 1

Author

Commented:
rqcharan

are you sure your code is correct?  Doesn't the 1st part equate to 0?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
To flesh out emoreau's response, create a table like so:
CREATE TABLE nonWorkDays (nonWorkDay SMALLDATETIME)

Fill it with week-ends and holidays.  Then you can do the days calculation something like this:

UPDATE tblCallBack_Data_m
SET LagTime = DATEDIFF(DAY, Inbox_Arrival, date_Closed) - (SELECT COUNT(*) FROM nonWorkDays WHERE nonWorkDay BETWEEN tblCallBack_Data_m.Inbox_Arrival AND tblCallBack_Data_m.date_Closed)
Where Closed = 1
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Below is sample code to load week-end dates.  I suggest creating, by hand, a separate table of holidays and inserting those into the nonWorkDays table as needed.

DECLARE @startDate SMALLDATETIME
DECLARE @endDate SMALLDATETIME
SET @startDate = 'Jan 01 2002'
SET @endDate = 'Dec 31 2004'  --change as needed
SET NOCOUNT ON
WHILE @startDate <= @endDate
BEGIN
     IF LEFT(DATENAME(WEEKDAY, @startDate), 3) IN ('Sat', 'Sun')
         INSERT INTO nonWorkDays VALUES(@startDate)
     SET @startDate = DATEADD(DAY, 1, @startDate)
END --WHILE
SET NOCOUNT OFF
--SELECT COUNT(*) FROM nonWorkDays

Author

Commented:
Works perfect, both Suggestion were used.

Thanks for the help
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Thanks, but I just thought of one other thing -- I didn't account for the time.  By default the time, if not specified, is midnight (00:00), so all times in the nonWorkDay table are midnight.  But that means if the "inbox_Arrival" or "date_Closed" are either/both a week-end or holiday, either/both will still not match the nonWorkDay table value.  For example, if "inbox_Arrival" is July 4th at 2:00pm, then July 4th at 0:00am won't be counted as a holiday.  

There are two ways to account for the time: (1) add a virtual (computed) column to the nonWorkDay table, with a time of 23:59, to serve as the end datetime; the original nonWorkDay will serve as the begin time. (2) change the query to account for time.

Method (1):

First, define the computed column:
ALTER TABLE nonWorkDays
ADD nonWorkDayEnd AS nonWorkDay + ' 23:59'

Then, change the UPDATE statement like so:

UPDATE tblCallBack_Data_m
SET LagTime = DATEDIFF(DAY, Inbox_Arrival, date_Closed) - (SELECT COUNT(*) FROM nonWorkDays WHERE nonWorkDay >=
tblCallBack_Data_m.Inbox_Arrival AND nonWorkDayEnd <=  
tblCallBack_Data_m.date_Closed)
Where Closed = 1

Method (2) to follow shortly ...
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Method (2):

UPDATE tblCallBack_Data_m
SET LagTime = DATEDIFF(DAY, Inbox_Arrival, date_Closed) -
(SELECT COUNT(*)
 FROM nonWorkDays
 WHERE nonWorkDay BETWEEN CONVERT(VARCHAR(11), tblCallBack_Data_m.Inbox_Arrival, 120) + ' 00:00'
 AND CONVERT(VARCHAR(11), tblCallBack_Data_m.date_Closed, 120) + ' 23:59')
Where Closed = 1

This is probably simpler once you get used to seeing the date manipulations.  This method will take very slightly more CPU for the date calculations, but I doubt you'd actually notice the diffence unless you were updating a lot of rows (50K+) with this statement.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial