1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| with cte_data (dossier, evt, dt_evt) as
(
select 1, 1, to_date('2021-01-01 12:01:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 1, 5, to_date('2021-01-03 11:05:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 1, 6, to_date('2021-01-04 10:07:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 2, 1, to_date('2021-01-01 09:45:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 2, 5, to_date('2021-01-03 08:23:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 2, 6, to_date('2021-01-04 07:05:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 2, 5, to_date('2021-01-06 06:32:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 2, 6, to_date('2021-01-12 05:15:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 3, 1, to_date('2021-01-01 04:59:00', 'yyyy-mm-dd hh24:mi:ss') from dual
)
, cte_grp (dossier, evt, dt_evt, grp) as
(
select dossier, evt, dt_evt
, floor((row_number() over(partition by dossier order by dt_evt) - 1)/ 2) as grp
from cte_data
where evt in (5, 6)
)
, cte_pivot (dossier, dt_evt_5, dt_evt_6) as
(
select dossier
, max(case evt when 5 then dt_evt end) as dt_evt_5
, max(case evt when 6 then dt_evt end) as dt_evt_6
from cte_grp
group by dossier, grp
)
select dossier, sum(dt_evt_6 - dt_evt_5)
from cte_pivot
group by dossier; |
Partager