How to calculate time difference excluding "Off" hours?

[HiBoo]

I need some help creating a function that will return a string formated like hh:mm:ss. The function will have two parameters and is set up as follows...

Create Function ftnGetElapsedTime(@x_StartDate DATETIME, @x_EndDate DATETIME)
Returns CHAR(08)

The business logic excludes "Off" hours in the calculation. "Off" hours are between "03:00:00" and "10:00:00" daily.

So for example,
StartTime EndTime Return
2005-01-01 01:00:00 2005-01-01 12:00:00 05:00:00
2005-01-01 03:30:00 2005-01-01 09:30:00 00:00:00
2005-01-01 02:30:00 2005-01-02 01:00:00 15:30:00
2005-01-01 12:00:00 2005-01-01 12:15:00 00:15:00

The purpose of the function is to calculate the working time difference between the Start and End dates excluding the "Off" hours.

 

[bboffin]

Assuming that the start and end dates will never fall in the Off hours then try something like this:

DECLARE @DateDiff datetime
DECLARE @HDiff int
DECLARE @Result as char(8)
-- get the difference between the dates
SET @DateDiff = @x_EndDate - @x_StartDate
-- get the number of days between the dates and multiply by 7 (the number of Off hours)
-- this may need to be adjusted by adding one if the start date time is after midnight
-- and before 3am 
SET @HDiff = 7 * DATEDIFF(day, @x_StartDate, @x_EndDate)
-- adjust the difference by this number of hours
SET @DateDiff = dateadd(hh, - @HDiff, @DateDiff)
SET @Result =  cast(datepart(d, @DateDiff) * 24 + datepart(hh, @DateDiff) as varchar) + ':' + cast(datepart(mi, @DateDiff) as varchar) + ':' + cast(datepart(ss, @DateDiff) as varchar)

RETURN @Result

I haven't validated this in any way but it might give you a start. It also doesn't take into account weekends or holidays!


Bob Boffin

 

[HiBoo]

Thanks Bob, I think this is an excellent start. Actually it is possible for either of the start or end date parameters to fall within the "off" hours. Say for example the start time was at 03:30:00 and the end date was at 12:00:00 on the same day. The result should be 02:00:00 because the hours between 03:00 and 10:00 are excluded. Also, since weekend hours are included in the calculation there is no need to account for it.

I'll do some more tweeking and post a solution if I happen to come up with one soon.

 

[HiBoo]

So this is what I've come up with so far as a solution to my problem. I've since changed the return value to return a REAL number representing the number of hours elapsed. I get this number from dividing the total calculated elapsed seconds by 60 to get minutes and then by 60 again to get hours. For my requirements this works fine.

If anyone can reduce the amount of code required to do this I'd really like to hear from you. Thanks in advance...

ALTER FUNCTION dbo.FN_GetTicketElapsedTime
(
         @o_StartDate DATETIME
       , @o_EndDate   DATETIME
)
RETURNS REAL
AS 
BEGIN
     DECLARE @x_StartDate          AS CHAR(08)     -- yyyymmdd
           , @x_StartTime          AS CHAR(08)     -- hh:mm:ss   (military)
           , @x_EndDate            AS CHAR(08)
           , @x_EndTime            AS CHAR(08)
           , @x_ElapsedSeconds     AS REAL
           , @x_condteOffStartTime AS CHAR(08)
           , @x_condblOffEndTime   AS CHAR(08)
           , @x_condblDayEndTime   AS CHAR(08)
           , @x_IntervalDays       AS INTEGER

  
     --Provide default for error cases
     SELECT @x_ElapsedSeconds = 0
  
     If (@o_StartDate < @o_EndDate)
      BEGIN
          --Determine actual value for all others
    
          -- Set the ticket start and end dates and times
          SELECT @x_StartDate          = CONVERT(CHAR(08), @o_StartDate, 112)
               , @x_StartTime          = CONVERT(CHAR(08), @o_StartDate, 108)
               , @x_EndDate            = CONVERT(CHAR(08), @o_EndDate, 112)
               , @x_EndTime            = CONVERT(CHAR(08), @o_EndDate, 108)
-- Excluded from the time calculations are 3am to 10am 
               , @x_condteOffStartTime = '03:00:00'
               , @x_condblOffEndTime   = '10:00:00'
               , @x_condblDayEndTime   = '23:59:59'

          -- 2.0 Ticket opened and closed on the same day.
          If (@x_StartDate = @x_EndDate) 
               -- 2.1 Early-morning opening before start of cutoff
               If (@x_StartTime < @x_condteOffStartTime) 
                BEGIN
                    If (@x_EndTime < @x_condteOffStartTime)  --early close too
                         SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_StartTime,@x_EndTime)
                    Else                                      --closes later - into cutoff time
                     BEGIN
                         SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_StartTime,@x_condteOffStartTime)
                         If (@x_EndTime > @x_condblOffEndTime)    --closes after end of cutoff
                              SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + (DATEDIFF(second,@x_condblOffEndTime, @x_EndTime))
                         -- ELSE there is nothing to calculate
                     END
                END
               -- 2.2 Opening within cutoff period
               Else
                BEGIN
                    If ((@x_StartTime >= @x_condteOffStartTime) And (@x_StartTime < @x_condblOffEndTime))
                     BEGIN
                         If (@x_EndTime >= @x_condblOffEndTime)  --closes later - after cutoff end
                              SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + (DATEDIFF(second,@x_condblOffEndTime, @x_EndTime))
                         -- ELSE close in cutoff too ,so nothing to calculate
                     END
                    -- 2.3 Opening after cutoff (closes same day)
                    Else
                         SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_StartTime, @x_EndTime)
                END
          --3. Ticket opened and closed on different days    
          Else
           BEGIN
               -- 3.1 First day
               --   3.1.1 Early-morning opening before start of cutoff
               If (@x_StartTime < @x_condteOffStartTime)
                BEGIN
                    SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_StartTime,@x_condteOffStartTime)   --initial period
                    SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + (DATEDIFF(second,@x_condblOffEndTime, @x_condblDayEndTime)) --rest of day 1
                END
               --   3.1.2 Opening within cutoff period
               Else
                BEGIN
                    If ((@x_StartTime >= @x_condteOffStartTime) And (@x_StartTime < @x_condblOffEndTime))
                         SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_condblOffEndTime, @x_condblDayEndTime)   --rest of day 1
                    --   3.1.3 Opening after cutoff (closes some future day)
                    Else
                         SELECT @x_ElapsedSeconds = DATEDIFF(second,@x_StartTime,@x_condblDayEndTime)
                END
              -- 3.2 Intervening full days (in example 00:00-03:00 and 10:00-24:00 added in)
              SELECT @x_IntervalDays = DATEDIFF(day, @o_StartDate, @o_EndDate)      
              If (@x_IntervalDays > 1)                       -- In the calculation, exclude the last day, will be calculated later in 3.3
                   SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + ((@x_IntervalDays - 1) * (DATEDIFF(second,'00:00:00', @x_condteOffStartTime) + DATEDIFF(second,@x_condblOffEndTime,@x_condblDayEndTime) ))

              -- 3.3 Closing day

              --   3.3.1 Early-morning close before start of cutoff
              If (@x_EndTime < @x_condteOffStartTime)
                   SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + DATEDIFF(second,'00:00:00',@x_EndTime)

              --   3.3.2 Close within cutoff period
              Else
               BEGIN
                   If ((@x_EndTime >= @x_condteOffStartTime) And (@x_EndTime < @x_condblOffEndTime))
                        SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + DATEDIFF(second,'00:00:00',@x_condteOffStartTime)
    
                   --   3.3.3 Opening after cutoff
                   Else
                        SELECT @x_ElapsedSeconds = @x_ElapsedSeconds + (DATEDIFF(second,'00:00:00',@x_condteOffStartTime) + (DATEDIFF(second,@x_condblOffEndTime, @x_EndTime)))
               END
           END

      END  
     --4. Convert value to elapsed hours

     SELECT @x_ElapsedSeconds = ((@x_ElapsedSeconds /60)/60)

RETURN @x_ElapsedSeconds 
END