Here is a link to a solution:

http://www.sqlservercentral.com/Forums/Topic412112-150-1.aspx#bm412179

@Barry62

Can you incorporate that with my existing query? I am a novice at SQL. Not sure how I would incorporate the link to my query.

UPDATE: when I reference the link I meant the formula suggested on that site not actually incorporating the link into the formula (just wanted to clarify after re-reading my comment) not that novice!

Thank you,

SELECT
  DateReceived
, TimeReceived
, (DateReceived + cast(cast(TimeReceived as time) as datetime)) AS [Combined Date and Time Received]
, DateClosed 
, TimeClosed
, (DateClosed + cast(cast(TimeClosed as time) as datetime))     AS [Combined Date and Time Closed]
, datediff(minute, (DateReceived + cast(cast(TimeReceived as time) as datetime))
-(CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, DateReceived) = 'Saturday' THEN 1 ELSE 0 END), 
(DateClosed + cast(cast(TimeClosed as time) as datetime))) 
  AS [Minutes from Receipt to Closure]
FROM tableX
WHERE 
DateReceived >= @from
  AND DateReceived <  @until

Select allOpen in new window

hi, I recognize that code.

there is a scalar function here that will calcuate the net working days (networkdays),  and also a more thorough one here that will calculate working time in minutes (i.e. exclude holidays and only include the standard working day.

The second one was modified to return a varchar in  a "day hour minute pattern" or it provides minutes as a varchar, but can be easily modified back to int.

In your calculation of [Minutes from Receipt to Closure] do you want a 24 hour day, or an 8 hour day? (plus exclude weekends)

Both of these are scalar functions, so once you create them they are used in your query like this:

,dbo.fcn_MadeForThis( (DateReceived + cast(cast(TimeReceived as time) as datetime)), (DateClosed + cast(cast(TimeClosed as time) as datetime)))
  AS [Minutes from Receipt to Closure]

In other words, it would simply replace the existing datediff() which is also a scalar function.

So, 8 hour day or 24 hour day, what is your preference?  (plus exclude weekends)

PortletPaul! Hello! Yes that is your original Query! THANK YOU!

@ Barry62 - Your query yielded the same result as the original query. The date received is almost never on a weekend (though it does happen occasionally) but rather a weekend may go by before we respond. For example:

I receive an order request on Friday at 4:15PM I don't respond to the service request until Monday at 8:30 AM. On original; calculation I get over 2 days as the response time but it should be 45 minutes (Office hours 8:30AM-5PM, - Saturday, - Sunday). But I do want to account for those occasions that a request does come in over the weekend and calculate accordingly.

PortletPaul - How would I incorporate that into the original query does that get appended to the end? I get an error if I appended to the end.

no not append, substitute, but you need the function to exist...

8 hour day or 24 hour day?

@evengeekier - You make a very good point.  I won't ask how you knew about PortletPaul's 'original query'.  I found it on a forum to which I showed the link.

I have revised it a bit to correct some errors(I think):

SELECT
  DateReceived
, TimeReceived
, (DateReceived + cast(cast(TimeReceived as time) as datetime)) AS [Combined Date and Time Received]
, DateClosed 
, TimeClosed
, (DateClosed + cast(cast(TimeClosed as time) as datetime))     AS [Combined Date and Time Closed]
, datediff(minute, (DateReceived + cast(cast(TimeReceived as time) as datetime))
,(DateClosed + cast(cast(TimeClosed as time) as datetime)))
-(CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN 1440 ELSE 0 END)
-(CASE WHEN DATENAME(dw, DateClosed) = 'Saturday' THEN 1440 ELSE 0 END) 
+(CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN datediff(minute, (DateReceived + cast(cast('17:00' as time) as datetime))) ELSE 0 END)
+(CASE WHEN DATENAME(dw, DateRecieved) = 'Saturday' THEN datediff(minute, (DateReceived + cast(cast('17:00' as time) as datetime))) ELSE 0 END)
  AS [Minutes from Receipt to Closure]
FROM tableX
WHERE 
DateReceived >= @from
  AND DateReceived <  @until

Select allOpen in new window

@Barry62 - the query that I included on my post with my question was provided to me by PortletPaul on another question I asked on EE last week. Since then I have been asked to deduct WeekEnds and Non-business hours. From that calculation.

As for your provided query  - It yielded the following Error (what change should I make):



@PortletPaul
Forgive me Paul, but as you know I am a novice at SQL, when you state that I have to create the function first what do you mean? Will that modify the database in anyway? I would prefer the 8 Hours calculation. Our Business hours are 8:30AM-5PM M-F. I am not concerned with holidays for the calculation.

Thank you both for your patience with me on this.

>> will it alter my database
yes, it will add one function

>> will it blow-up my database and the city I live in?
no

All you do is get the code, like a query, and execute it (and assuming you have the rights to create functions it'll work)

you can practice as sqlfiddle
just go to http://sqlfiddle.com

in a drop down pick sql server 2008 (or 2102)

copy that function code block, and paste into the left hand panel at sqlfiddle, then click 'build schema'

it isn't scary

if you do not have the rights to create this function, find someone who can..

>>Our Business hours are 8:30AM-5PM M-F.
those business hours include a 'lunch' period, which the function simply ignores.
for times prior to 9:00am it assumes them to be 9:00am, and by doing it this way it effectively ignores that lunch break, now this might have some unfortunate impacts because in many cases the whole elapsed time isn't too long in your data. We can probably tweak the function further to make it work 8:30 17:00 but that is 8.5 hours not 8. Try it as is first please but look for test cases where the start time is in the range 08:30 - 09:00

>>I am not concerned with holidays for the calculation.
good, I took out that piece.
The function's author had allowed for this but you need a table of holiday dates

Sorry.  Here:

SELECT
  DateReceived
, TimeReceived
, (DateReceived + cast(cast(TimeReceived as time) as datetime)) AS [Combined Date and Time Received]
, DateClosed 
, TimeClosed
, (DateClosed + cast(cast(TimeClosed as time) as datetime))     AS [Combined Date and Time Closed]
, datediff(minute, (DateReceived + cast(cast(TimeReceived as time) as datetime))
,(DateClosed + cast(cast(TimeClosed as time) as datetime)))
-(CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN 1440 ELSE 0 END)
-(CASE WHEN DATENAME(dw, DateClosed) = 'Saturday' THEN 1440 ELSE 0 END) 
+(CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN datediff(minute, (DateReceived + cast(cast(TimeReceived as time) as datetime)),(DateReceived + cast(cast('17:00' as time) as datetime))) ELSE 0 END)
+(CASE WHEN DATENAME(dw, DateReceived) = 'Saturday' THEN datediff(minute, (DateReceived + cast(cast(TimeReceived as time) as datetime)),(DateReceived + cast(cast('17:00' as time) as datetime))) ELSE 0 END)
  AS [Minutes from Receipt to Closure]
FROM tableX
WHERE 
DateReceived >= @from
  AND DateReceived <  @until

Select allOpen in new window

@Barry62, nice case statement - took me a while to 'get it'

@evengeekier, if you pursue this method, you also have to adjust the original datediff's returned number (line 8) this isn't in working minutes so there is more maths to be performed.

SELECT
   DateReceived
 , TimeReceived
 , (DateReceived + cast(cast(TimeReceived AS TIME) AS DATETIME)) AS [Combined Date and Time Received]
 , DateClosed
 , TimeClosed
 , (DateClosed + cast(cast(TimeClosed AS TIME) AS DATETIME)) AS [Combined Date and Time Closed]
 , datediff(minute, (DateReceived + cast(cast(TimeReceived AS TIME) AS DATETIME)), (DateClosed + cast(cast(TimeClosed AS TIME) AS DATETIME)))
     - (  CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN 1440 ELSE 0 END  ) 
     - (  CASE WHEN DATENAME(dw, DateClosed) = 'Saturday' THEN 1440 ELSE 0 END  ) 
     + (  CASE WHEN DATENAME(dw, DateReceived) = 'Sunday' THEN 
               datediff(minute, (DateReceived + cast(cast(TimeReceived AS TIME) AS DATETIME)), (DateReceived + cast(cast('17:00' AS TIME) AS DATETIME)))
          ELSE 0 END  ) 
     + (  CASE WHEN DATENAME(dw, DateReceived) = 'Saturday' THEN 
               datediff(minute, (DateReceived + cast(cast(TimeReceived AS TIME) AS DATETIME)), (DateReceived + cast(cast('17:00' AS TIME) AS DATETIME)))
          ELSE 0 END  ) 
    AS [Minutes from Receipt to Closure]
FROM tableX
WHERE DateReceived >= @from AND DateReceived < @until

Select allOpen in new window

@PortletPaul -Yes I have admin rights, even better I have a test database with a duplicate dataset. I added the function ran the query. Excellent result! THANK YOU!

If I wanted to now compare a different set of dates (i.e DateReceived, TimeReceived to DateOpened, TimeOpened) would I need to create a new function? or just change the query?

(I would test it but had to step away from my workstation)

Thank you.

@Barry62 - Your query returned the same result as with the weekends etc. Thank you for your help in either case.

>>If I wanted to now compare a different set of dates ... would I need to create a new function?
do you need a different dateadd() function for each query?

NO, absolutely not. The whole point is you put some piece of logic into a function to re-use it.

you simply put different date fields into the function - just like dateadd() or datediff() - these are also functions.

you can use it in any query where you need this logic (as long as you can access the function of course)

@PortletPaul - Thank you.  I am not a novice with SQL, but I don't know nearly enough.  I'll take all the encouragement I can get! :)

@evengeekier - I tried. :)  I think I will defer to PortletPaul for this solution.  I will be saving it for future reference, though.

@PortletPaul - So it works with other dates as well. Thank you. Would changing the Business Hours to 8:30AM to 5PM be possible with the above?

thank you.

before trying to make that change please prove it is wrong first.. ok?

So I tested it on an order received before 9AM:

Date Received = 3/11/2013 Time Received =7:00AM
Date Closed = 3/19/2013 Time Closed = 8:53PM

dbo.NetWorkMins = 3360

Should be 3570 right? (8.5 hours * 7 days (excludes weekends)

Date Received = 3/11/2013 Time Received =7:00AM -> treated as 09:00 that day
Date Closed = 3/19/2013 Time Closed = 8:53PM -> treated as 17:00 that day
elapsed 9 days, work days = 7, *8*60 = 3360

the real question is, do you consider the "work day" to be 8 hours or 8.5 hours.
Most of the time the .5 would be due to a lunch break and is excluded from calculation of work time...

If I make it 8.5 it will affect every full day too, not just those the exceptions that occur between 08:30-09:00; so a Time Received 12:00 day 1 and 12:00 day 2 = 8.5*60
savvy?
Is this really what you want? are you doubly sure it isn't an 8 hour day?

if you do want to make that 8.5 change, just replace current line 28, with this one:

    Declare @StartHour decimal(2,1)  = 8.5        /* Start of business hours (as an argument) */

then also change line 1 to start with:

ALTER FUNCTION

then re-run, it will update the function and now work with an 8.5 hour working day

@PortletPaul - Your explanation makes a lot of sense to me. I asked the powers that be and they agree on the 8 hour work day. Thank you again.

PortletPaul is a SQL genius!

Ok, great.

I have one further idea of this topic I will experiment with...

if a start time is >= 8:30 and < 9:00 I will add 30 minute to both start time and finsh time
this will 'push' those start time into scope

I think this will work - stay tuned

OK, believe works, so you won't lose an arbitrary 30 minutes for starts 08:30 to 09:00.
You will need to change line 1 to read
Alter Function

Create Function NetWorkMins(
  @StartDate DateTime
, @EndDate DateTime
) 
Returns INT
AS
/*
origin:
http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates
Arjun Khosla
Sep 5 '12 at 7:57

Modified, PortetPaul Experts-Exchange.com for question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28077021.html#a39021904

aa. name change to NetWorkMins
a. removal of holiday table, code commented out
b. all comments use slash*, and some other cosmetics

USAGE
, dbo.NetWorkMins(Start,Finish) as [Working Minutes]


*/
Begin
    Declare @WorkMin int    = 0              /* Initialize counter */
    Declare @Reverse bit                     /* Flag to hold if direction is reverse */
    Declare @OpenAtHour time  = '08:30:00'   /* Actual Start of business hours  */
    Declare @StartHour int  = 9              /* Start of business hours  */
    Declare @EndHour int    = 17             /* End of business hours (as an argument) */
    /* Declare @Holidays Table (HDate DateTime) */   /*   Table variable to hold holidayes */

    /* If dates are in reverse order, switch them and set flag */
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime = @StartDate
        Set @StartDate = @EndDate
        Set @EndDate = @TempDate
        Set @Reverse = 1
    End
    Else Set @Reverse = 0

    /* Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema) */
    /* Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE = @Country and HDATE>= DateAdd(dd, DateDiff(dd,0,@StartDate), 0) */

    /* "push" timings 30 minutes if between real opening time and the 8 hour day timeframe */
    /* this is for a specific purpose and is not generic, store opens at 08:30 so to not truncate, push these conditions into consideration */
    If cast(@StartDate as time) >= @OpenAtHour AND DatePart(HH, @StartDate) < @StartHour
    Begin
       Set @StartDate = DateAdd(minute, 30 , @StartDate)
       Set @EndDate   = DateAdd(minute, 30 , @EndDate)
    End

    If DatePart(HH, @StartDate)<@StartHour  Set @StartDate = DateAdd(hour, @StartHour,    DateDiff(DAY, 0, @StartDate)) /* If Start time is less than start hour, set it to start hour */
    If DatePart(HH, @StartDate)>= @EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) /* If Start time is after end hour, set it to start hour of next day */
    If DatePart(HH, @EndDate)>= @EndHour+1   Set @EndDate   = DateAdd(hour, @EndHour,      DateDiff(DAY, 0, @EndDate))   /* If End time is after end hour, set it to end hour */
    If DatePart(HH, @EndDate)<@StartHour    Set @EndDate   = DateAdd(hour, @EndHour-24,   DateDiff(DAY, 0, @EndDate))   /* If End time is before start hour, set it to end hour of previous day*/

    If @StartDate>@EndDate Return 0

    /* If Start and End is on same day */
    If DateDiff(Day,@StartDate,@EndDate) <=  0
    Begin
        If Datepart(dw,@StartDate) > 1 And DATEPART(dw,@StartDate) < 7  /*  If day is between sunday and saturday */
            /* If (Select Count(*) From @Holidays Where HDATE = DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */ /*  If day is not a holiday */
                If @EndDate<@StartDate Return 0 Else Set @WorkMin = DATEDIFF(MI, @StartDate, @EndDate) /*  Calculate difference */
            /* Else Return 0 */
        Else Return 0
    End
    Else Begin
        Declare @Partial int = 1   /*  Set partial day flag */
        While DateDiff(Day,@StartDate,@EndDate) > 0   /*  While start and end days are different */
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    /*   If this is a weekday */
            Begin
                If 1 = 1 /* due to removal of holiday (see next line), leave 1 = 1 in lieu */ 
                         /* (Select Count(*) From @Holidays Where HDATE = DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */ /*  If this is not a holiday */
                Begin
                    If @Partial = 1  /*  If this is the first iteration, calculate partial time */
                    Begin 
                        Set @WorkMin = @WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial = 0 
                    End
                    Else Begin      /*  If this is a full day, add full minutes */
                        Set @WorkMin = @WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate) > 1 And DATEPART(dw,@StartDate) < 7  /*  If last day is a weekday */
            /* If (Select Count(*) From @Holidays Where HDATE = DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */  /*  And it is not a holiday */
            If @Partial = 0 Set @WorkMin = @WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin = @WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
   
    If @Reverse = 1 Set @WorkMin = -@WorkMin /* minus of value */
    Return @WorkMin
End
;

Select allOpen in new window

@PortletPaul - I made the changes you suggested above (thank you for going the extra mile on this). It worked here's an example:

Order Received on:
02/14/2013 @ 8:33AM
Closed On:
03/08/2013 @ 11:05AM

Function returns = 7832

Paul I am very grateful. Thank you again.

no problem, we are here because we enjoy assisting

don't forget that when it comes time to put this into 'production' you must have that function defined in that database :)

Cheers, Paul