Oracle, split a time duration multiple rows by one hour period

Question

I understand this question was answered here:

Oracle, split a time duration row by one hour period

but, what about if you have multiple time periods like this:

I have the following query:

select AdmitDate, PatientName, Room, greatest(start_time, trunc(start_time+(level-1)/24, 'hh24')) beginTime, least(end_time, trunc(start_time+(level)/24, 'hh24')) endTime
from Utilization
connect by level <= floor((trunc(end_time, 'hh')-trunc(start_time, 'hh'))*24)+1

but it returns the results below which appears to be incorrect.

If I have a single time row table it works great.

Is there a way I can group the results by PatientName or just each row calculated separatly?

score 1 · Accepted Answer · answered Jul 25 '18 at 02:52

You're really very close. Add a DISTINCT and an ORDER BY and I think you've got the results you were looking for:

SELECT DISTINCT AdmitDate,
                PatientName,
                Room,
                greatest(start_time, trunc(start_time+(level-1)/24, 'hh24')) beginTime,
                least(end_time, trunc(start_time+(level)/24, 'hh24')) endTime
  from Utilization
  connect by level <= floor((trunc(end_time, 'hh')-trunc(start_time, 'hh'))*24)+1
  ORDER BY 1, 2, 3, 4, 5

This produces:

ADMITDATE               PATIENTNAME ROOM BEGINTIME              ENDTIME
2012-01-24 00:00:00     Patient1    RM1  2012-01-24 07:30:00    2012-01-24 08:00:00
2012-01-24 00:00:00     Patient1    RM1  2012-01-24 08:00:00    2012-01-24 08:32:00
2012-01-24 00:00:00     Patient2    RM1  2012-01-24 08:45:00    2012-01-24 09:00:00
2012-01-24 00:00:00     Patient2    RM1  2012-01-24 09:00:00    2012-01-24 09:13:00

SQLFiddle here

Oracle, split a time duration multiple rows by one hour period

1 Answers1