CODEWITHSUNDEEP
sqloracle-databasedateoracle11g
jaxonjma
jaxonjma

Reputation: 307

Time elapsed between two dates (In a specific time range) ORACLE

I am creating a query that shows me the time elapsed between two dates, only taking into account only the one that is Monday through Friday from 08:00 to 17:00, for example:

For example, if a petition opens on day 1 at 6:30 p.m. and closes on day 2 at 8:45 p.m., the TMO is 45 minutes. If it closes on day 3 at 8:45, the TMO is 9 hours and 45 minutes.

Example 2: If a petition opens on Friday at 16:45 and closes on Tuesday at 8:30, the MTO would be: 15 minutes on Friday, nine hours on Monday and 30 minutes on Tuesday for an MTO = 9 hours 45 minutes

The query is performed on a single column of type date as I show below

introducir la descripción de la imagen aquí

I currently use a LAG function to make the query, but I can not create something functional, not even optimal to incorporate, I would greatly appreciate your help.

Upvotes: 0

Views: 131

Answers (1)

user5683823
user5683823

Reputation:

In the solution below I will ignore the "lag" part of your problem, which you said you know how to use. I am only showing how to count "working hours" between any two date_times (they may be during or before or after work hours, and/or they can be on weekend days; the computation is the same in all cases).

Explaining the answer in words: For two given date-times, "start" and "end", calculate how many "work" hours elapsed from the beginning of the week (from Monday 00:00:00) till each of them. This is in fact a calculation for ONE date, not for TWO dates. Then: given "start" and "end", calculate this number of hours for each of them; subtract the "end" number of hours from the "start" number of hours. To the result, add x times 5 times 9, where x is the difference in weeks between Monday 00:00:00 of the two dates. (If they are in the same week, the difference will be 0.)

To truncate a date to the beginning of the day, we use TRUNC(dt). To truncate to the beginning of Monday, TRUNC(dt, 'iw').

To compute how many "work" hours are from the beginning of the date dt until the actual time-of-day we can use the calculation

greatest(0, least(17/24, dt - trunc(dt)) - 8/24)

(the results will be in days; we calculate everything in days and then we can convert to hours). However, in the final formula we must check to see if the date is a Saturday or Sunday, in which case this should just be zero. Or, better, we can adjust the calculation a bit later, when we count from the beginning of Monday (we can use least( 5*9/24, ...)).

Putting everything together:

with
  inputs ( dt1, dt2 ) as (
    select to_date('2017-09-25 11:30:00', 'yyyy-mm-dd hh24:mi:ss'),
           to_date('2017-10-01 22:45:00', 'yyyy-mm-dd hh24:mi:ss')
    from   dual
  )
-- End of SIMULATED input dates (for testing only).
select 24 *
     ( least(5 * (17 - 8) / 24, greatest(0, least(17/24, dt2 - trunc(dt2)) - 8/24)
                                + (17 - 8) / 24 * (trunc(dt2) - trunc(dt2, 'iw'))) 
       -
       least(5 * (17 - 8) / 24, greatest(0, least(17/24, dt1 - trunc(dt1)) - 8/24)
                                + (17 - 8) / 24 * (trunc(dt1) - trunc(dt1, 'iw'))) 
       + 5 * (17 - 8) / 24 * (trunc(dt2, 'iw') - trunc(dt1, 'iw')) / 7
     )
       as duration_in_hours
from   inputs
;


DURATION_IN_HOURS
-----------------
           41.500

Upvotes: 1

Related Questions