Sql Formula that calculates the workweek and Excludes weekends

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

This takes care of weekends

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

rqcharan

are you sure your code is correct?  Doesn't the 1st part equate to 0?

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

Works perfect, both Suggestion were used.

Thanks for the help

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

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.