|
Контроль регламентного времени запросом (расписание)
#39918047
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Ukraine,Lviv
Сообщения: 4 370
|
|
(o-O),
пропущено...
для 07 query_7 11:30:00 время просрочки 11:40:00?
[
[/src]
......
stax
Да
тогда надо учитивать время погашения
влоб
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.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
with files as
(
select 'Q' Type ,'01' as id, 'query_1' as name, TO_DATE('22.01.2020 11:00:00','DD.MM.YYYY hh24:mi:ss') dt, '61' as par_id from dual union all
select 'Q','02' as id, 'query_2' as name, TO_DATE('22.01.2020 11:00:01','DD.MM.YYYY hh24:mi:ss'), '62' as par_id from dual union all
select 'Q','03' as id, 'query_3' as name, TO_DATE('22.01.2020 11:00:02','DD.MM.YYYY hh24:mi:ss'), '63' as par_id from dual union all
select 'Q','04' as id, 'query_4' as name, TO_DATE('22.01.2020 11:00:03','DD.MM.YYYY hh24:mi:ss'), '64' as par_id from dual union all
select 'Q','05' as id, 'query_5' as name, TO_DATE('22.01.2020 11:00:04','DD.MM.YYYY hh24:mi:ss'), '65' as par_id from dual union all
select 'A','61' as id, 'a_1' as name, TO_DATE('22.01.2020 11:05:05','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'A','62' as id, 'a_2' as name, TO_DATE('22.01.2020 11:15:05','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'A','63' as id, 'a_3' as name, TO_DATE('22.01.2020 11:25:05','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'A','64' as id, 'a_4' as name, TO_DATE('22.01.2020 11:26:05','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'A','65' as id, 'a_5' as name, TO_DATE('22.01.2020 11:25:05','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'Q','07' as id, 'query_7' as name, TO_DATE('22.01.2020 11:30:00','DD.MM.YYYY hh24:mi:ss'), '8' as par_id from dual union all --просрочка
select 'A','08' as id, 'a_7' as name, TO_DATE('22.01.2020 11:45:00','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'Q','09' as id, 'q_9' as name, TO_DATE('22.01.2020 11:45:00','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'Q','10' as id, 'q_10' as name, TO_DATE('22.01.2020 11:45:00','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual union all
select 'Q','11' as id, 'q_11' as name, TO_DATE('22.01.2020 11:45:00','DD.MM.YYYY hh24:mi:ss'), '12' as par_id from dual union all --просрочка
select 'A','12' as id, 'a_11' as name, TO_DATE('22.01.2020 12:20:00','DD.MM.YYYY hh24:mi:ss'), '' as par_id from dual
)
,f as (
select f1.*
,f2.dt dt_answer
--,rownum rw --rowid
from files f1,files f2
where f2.id(+)=f1.par_id
)
select q.*
,decode(q.type,'Q',q.dt+
(select count(*)
from f
where q.type='Q' and f.type='Q'
and f.dt<=q.dt
and f.id<=q.id --rowid
and (dt_answer>q.dt or dt_answer is null)
)*1/24/6) last_term
from f q --files q
order by q.dt,q.id
/
T ID NAME DT PA DT_ANSWE LAST_TER
- -- ------- -------- -- -------- --------
Q 01 query_1 11:00:00 61 11:05:05 11:10:00
Q 02 query_2 11:00:01 62 11:15:05 11:20:01
Q 03 query_3 11:00:02 63 11:25:05 11:30:02
Q 04 query_4 11:00:03 64 11:26:05 11:40:03
Q 05 query_5 11:00:04 65 11:25:05 11:50:04
A 61 a_1 11:05:05
A 62 a_2 11:15:05
A 63 a_3 11:25:05
A 65 a_5 11:25:05
A 64 a_4 11:26:05
Q 07 query_7 11:30:00 8 11:40:00
A 08 a_7 11:45:00
Q 09 q_9 11:45:00 12:05:00
Q 10 q_10 11:45:00 12:15:00
Q 11 q_11 11:45:00 12 12:20:00 12:25:00
A 12 a_11 12:20:00
16 rows selected.
.....
stax |
|
|
