Calculating Average time with bussiness hours taken into account

[webron]

My order table consist of 2 fields
Time_RX (datetime) dateTime order has been placed
Time_TX (datetime) dateTime order has been handled

Now I have to calculate the responsetime between placing an handling, with bussiness hours taken into account!

What I mean is:

Suppose Bussinesshours : 08:00 - 17:30

Suppose an order has been placed at 16:30, but is handled the next day at 09:00.
ResponseTime should be
16:30 - 17:30 (1 hour)
08:00 - 09:00 (1 hour)
2 hours

Is it possible to build a query doing this?

 

[donutman]

Yes, it will however get a bit messy if you need to consider which days of the week count (weekends & holidays). What say you?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jdgonzalez]

I created something similar a few months ago. It's not perfect, but it may get you started. This function takes two parameters: a start and an end date. It returns the number of business hours between the two dates and excludes Saturday and Sunday. You can then just average the output of the function (which is how I use it).

CREATE function dbo.GetBusinessHours
/*******************************************************/
/* Written by:	J.D. Gonzalez to include busines hours */
/* Purpose:  Provide the number of business hours      */
/* 		between two dates.  Business days      */
/*		are classified as Monday - Friday      */
/*******************************************************/
(
     @StartDate datetime,
     @EndDate datetime
) 
returns decimal(9,2)
 as
begin
/*Variable declaration */
  declare @HoursBetween decimal(9,2) /* calculated number of hours between startdate and enddate */
  declare @minutesBetween decimal(9,2) /* number of minutes between startdate and enddate */
  declare @BusinessHours decimal(9,2) /* calculated number of business hours */
  declare @Cnt int /* temp counter used to add the hour to the startdate */
  declare @EvalDate datetime /* temp date used in evaluation */
  declare @strip as decimal(9,2) /* strips out decimal from @hours between */

  select @HoursBetween = 0 /* set @hoursbetween to zero */
  select @BusinessHours = 0 /* set @businessHours to zero */
  select @Cnt=0 /* set the counter to zero */

  select @minutesBetween = datediff(mi,@StartDate,@endDate) /* determine number of minutes between start and end dates */
  select @HoursBetween = @minutesBetween/60 /* change to hours */
  select @strip = right(@hoursBetween,3) /* strip out decimal from @hoursbetween */

/* Program logic */
   while @Cnt < @HoursBetween /*cycle through the entire interval of hours */
     begin
          select @EvalDate = dateadd(hh,@cnt,@StartDate) /* getdate to evaluate */
		/* add one hour if the does not fall on a Saturday or Sunday */
                if (((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7)) and
			(datepart(hh,@EvalDate) not in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)))
			/*Indicates what times not to count -- 00:00am to 7:59am, noon, 05:00pm to 11:59pm*/
                    BEGIN
                         select @BusinessHours = @BusinessHours + 1
                    END
          select @Cnt = @Cnt + 1 /* Increment counter */
    end
 select @BusinessHours = @BusinessHours + @strip /* add decimal portion back to @hours */
 return @BusinessHours /* return value back to calling statement */
end