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
| with cte_data_grp (res_id, res_Data, res_DateEvt, res_eve_id, grp) as
(
select res_id, res_Data, res_DateEvt, res_eve_id
, row_number() over( order by res_DateEvt asc)
- row_number() over(partition by res_eve_id order by res_DateEvt asc)
from RES
)
, cte_data_filter (res_id, res_Data, res_DateEvt, res_eve_id, grp, flt) as
(
select res_id, res_Data, res_DateEvt, res_eve_id, grp
, case res_eve_id
when 600 then row_number() over(partition by res_eve_id, grp order by res_DateEvt asc)
when 601 then row_number() over(partition by res_eve_id, grp order by res_DateEvt desc)
end
from cte_data_grp
)
, cte_data_pair (res_id, res_Data, res_DateEvt, res_eve_id, pair) as
(
select res_id, res_Data, res_DateEvt, res_eve_id
, (row_number() over(order by res_DateEvt asc) - 1)/ 2
from cte_data_filter
where flt = 1
)
select min(res_Data) as evt_600
, max(res_Data) as evt_601
, datediff(second, min(res_Data), max(res_Data)) as duree
from cte_data_pair
group by pair
order by pair asc;
evt_600 evt_601 duree
--------------------------- --------------------------- -----
2022-02-08 14:18:30.0000000 2022-02-08 14:33:45.0000000 915
2022-02-08 14:36:28.0000000 2022-02-08 14:37:44.0000000 76
2022-02-08 15:23:06.0000000 2022-02-08 15:24:45.0000000 99
2022-02-08 15:26:44.0000000 2022-02-08 15:28:15.0000000 91 |
