FORMATING SELECT

Question

I have a select which shows me how much lasts a process, it looks like:

SELECT
info1,
info2,
ifno3...
(DATE,'DD.MM.YYYY')DAY,
to_char(DATE,'HH24:MI') hour_from,
to_char(DATE_UNTIL,''HH24:MI'')hour_until,
REGEXP_SUBSTR((date_until-date)DAY TO SECOND,'d{2}:\d{2}')lasts_time
.....

and if a process lasts for example from 09.06.2021 23:00 to 10.06.2021 07:00, then the output of query from above will be:

info1  info2  info3    DAY        hour_from     hour_until   lasts_time
info1| info2 |info3 |09.06.2021 |23:00       | 07:00        | 08:00

and i want it to change in if this process lasts over night, then the output should be something like this:

info1  info2  info3    DAY        hour_from     hour_until   lasts_time
info1| info2 |info3 |09.06.2021 |23:00       | 00:00        | 01:00
info1| info2 |info3 |10.06.2021 |00:00       | 07:00        | 07:00

so it will count only until the day ends, and if the process is still on going in the new day the output will come in a new row,

I think I have here to deal with union all, but I'm not sure, if anyone has any idea would be great

user_9097906 · Answer

Here's one option. Read comments within code.

SQL> with
  2  test (id, info1, date_from, date_to) as
  3    -- sample data
  4    (select 1, 'info1', to_date('09.06.2021 23:00', 'dd.mm.yyyy hh24:mi'),
  5                        to_date('10.06.2021 07:00', 'dd.mm.yyyy hh24:mi')
  6     from dual
  7    ),
  8  temp as
  9    -- row generator, to generate all hours between DATE_FROM and DATE_TO
 10    (select id, info1,
 11            date_from + (level - 1) / 24 datum,
 12            lead(date_from + (level - 1) / 24) over (order by date_from) next_datum
 13     from test
 14     connect by level <= (date_to - date_from) * 24 + 1
 15    ),
 16  temp2 as
 17    -- find date boundaries (MINDAT and MAXDAT), as well as duration in between (LASTS_TIME)
 18    (select id, info1, datum, next_datum,
 19       min(datum) mindat,
 20       max(case when to_char(next_datum, 'mi') = '00' then next_datum - 1/(24*60*60)
 21                else datum
 22           end
 23          ) maxdat,
 24       --
 25       round((max(case when to_char(next_datum, 'mi') = '00' then next_datum - 1/(24*60*60)
 26                       else datum
 27                  end
 28                 ) -
 29              min(datum)
 30             ) * 24) lasts_time
 31     from temp
 32     group by id, info1, datum, next_datum
 33    )
 34  -- the final result
 35  select id,
 36    info1,
 37    to_char(datum, 'dd.mm.yyyy') day,
 38    min(to_char(datum, 'hh24:mi')) hour_from,
 39    max(to_char(next_datum, 'hh24:mi')) hour_until,
 40    sum(lasts_time) lasts_time
 41  from temp2
 42  group by id, info1,
 43           to_char(datum, 'dd.mm.yyyy')
 44  order by day;

The result is

        ID INFO1 DAY        HOUR_FROM  HOUR_UNTIL LASTS_TIME
---------- ----- ---------- ---------- ---------- ----------
         1 info1 09.06.2021 23:00      00:00               1
         1 info1 10.06.2021 00:00      07:00               7

SQL>

For @astentx's comment: there's no endless loop in my case for values you suggested:

SQL> set timing on
SQL> with
  2  test (id, info1, date_from, date_to) as
  3    -- sample data
  4    (select 1, 'info1', to_date('10.06.2021 07:00', 'dd.mm.yyyy hh24:mi'),
  5                        to_date('11.06.2021 07:00', 'dd.mm.yyyy hh24:mi')
  6     from dual
  7    ),


        ID INFO1 DAY        HOUR_ HOUR_ LASTS_TIME
---------- ----- ---------- ----- ----- ----------
         1 info1 10.06.2021 07:00 23:00         17
         1 info1 11.06.2021 00:00 07:00          7

Elapsed: 00:00:00.04
SQL>

user_2778710 · Answer

You can use recursive CTE to generate required amount of rows (which are equal to days distance between dates).

with a as (
  select
    'info1' as id
    , to_date('2021-05-01 10:20:30') as dt_from
    , to_date('2021-05-03 07:08:09') as dt_to
  from dual
  union all
  select
    'info2'
    , to_date('2021-05-02 05:06:07')
    , to_date('2021-05-02 10:20:30')
  from dual
)
, b(id, dt_from, dt_to, dt) as (
  select
    id
    , dt_from
    , dt_to
    , trunc(dt_from)
  from a

  union all

  select
    id
    , dt_from
    , dt_to
    , dt + 1
  from b
  where dt < trunc(dt_to)
)
select
  id
  , dt
  , numtodsinterval(
      case
        when trunc(dt_to) = dt
        then dt_to
        else dt + 1
      end
      -
      greatest(dt_from, dt)
    
    , 'DAY') as dur_interval
    
    /*Or any rounding function you need*/
  , trunc((case
        when trunc(dt_to) = dt
        then dt_to
        else dt + 1
      end
      -
      greatest(dt_from, dt))*24) as dur_hours
  , case
     when trunc(dt_from) = dt
     then to_char(dt_from, 'hh24:mi')
     else '00:00'
    end as hour_from
  , case
     when trunc(dt_to) = dt
     then to_char(dt_to, 'hh24:mi')
     else '23:59'
    end as hour_to
from b
order by
  id
  , dt

ID    | DT                  | DUR_INTERVAL                  | DUR_HOURS | HOUR_FROM | HOUR_TO
:---- | :------------------ | :---------------------------- | --------: | :-------- | :------
info1 | 2021-05-01 00:00:00 | +000000000 13:39:30.000000000 |        13 | 10:20     | 23:59  
info1 | 2021-05-02 00:00:00 | +000000001 00:00:00.000000000 |        24 | 00:00     | 23:59  
info1 | 2021-05-03 00:00:00 | +000000000 07:08:09.000000000 |         7 | 00:00     | 07:08  
info2 | 2021-05-02 00:00:00 | +000000000 05:14:23.000000000 |         5 | 05:06     | 10:20

db<>fiddle here

FORMATING SELECT

2 Answers