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.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
with t (wonum, status, changedate, wostatusid
) as ( select 1014, 'WAPPR', to_date('2021-10-03 15:25:16','yyyy-mm-dd hh24:mi:ss'), 181
from dual union all select 1014, 'APPR', to_date('2021-10-03 15:25:24','yyyy-mm-dd hh24:mi:ss'), 182
from dual union all select 1014, 'INPRG', to_date('2021-10-03 15:26:45','yyyy-mm-dd hh24:mi:ss'), 183
from dual union all select 1014, 'SLAHOLD', to_date('2021-10-03 15:29:33','yyyy-mm-dd hh24:mi:ss'), 184
from dual union all select 1014, 'INPRG', to_date('2021-10-03 15:29:41','yyyy-mm-dd hh24:mi:ss'), 185
from dual union all select 1014, 'SLAHOLD', to_date('2021-10-03 15:29:45','yyyy-mm-dd hh24:mi:ss'), 186
from dual union all select 1014, 'INPRG', to_date('2021-10-03 15:43:40','yyyy-mm-dd hh24:mi:ss'), 187
from dual union all select 1014, 'REJECT', to_date('2021-10-03 15:43:44','yyyy-mm-dd hh24:mi:ss'), 188
from dual union all select 1014, 'CLOSE', to_date('2021-10-07 10:00:21','yyyy-mm-dd hh24:mi:ss'), 2723
--
from dual union all select 1015, 'WAPPR', to_date('2021-10-03 15:25:16','yyyy-mm-dd hh24:mi:ss'), 181
from dual union all select 1015, 'APPR', to_date('2021-10-03 15:25:24','yyyy-mm-dd hh24:mi:ss'), 182
from dual union all select 1015, 'INPRG', to_date('2021-10-03 15:26:45','yyyy-mm-dd hh24:mi:ss'), 183
from dual union all select 1015, 'REJECT', to_date('2021-10-03 15:43:44','yyyy-mm-dd hh24:mi:ss'), 188
from dual union all select 1015, 'CLOSE', to_date('2021-10-07 15:25:16','yyyy-mm-dd hh24:mi:ss'), 2723
--
from dual union all select 1016, 'WAPPR', to_date('2021-10-03 15:25:16','yyyy-mm-dd hh24:mi:ss'), 181
from dual union all select 1016, 'APPR', to_date('2021-10-03 15:25:24','yyyy-mm-dd hh24:mi:ss'), 182
from dual union all select 1016, 'INPRG', to_date('2021-10-03 15:26:45','yyyy-mm-dd hh24:mi:ss'), 183
from dual union all select 1016, 'SLAHOLD', to_date('2021-10-03 15:29:45','yyyy-mm-dd hh24:mi:ss'), 186
from dual union all select 1016, 'INPRG', to_date('2021-10-04 15:29:45','yyyy-mm-dd hh24:mi:ss'), 187
from dual union all select 1016, 'REJECT', to_date('2021-10-04 15:43:44','yyyy-mm-dd hh24:mi:ss'), 188
from dual union all select 1016, 'CLOSE', to_date('2021-10-07 15:25:16','yyyy-mm-dd hh24:mi:ss'), 2723
from dual
)
,tt as (select t.*,lead(changedate) over (partition by wonum order by changedate) le from t)
select wonum,
(max(decode(status, 'CLOSE',changedate)) - min(decode(status, 'WAPPR',changedate))) - --всего
nvl(sum(decode(status, 'SLAHOLD',le-changedate)),0) --SLAHOLD
d
from tt
group by wonum
order by 1
SQL> /
WONUM D
---------- ----------
1014 3.76460648
1015 4
1016 3
