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
| declare @MaTable table (id varchar(10), ts datetime2, status tinyint);
insert into @MaTable values
('INS025067', '2021-03-12 03:12:53.000', 8),
('INS025067', '2021-03-12 15:19:24.000', 5),
('INS023857', '2021-02-10 17:31:45.000', 8),
('INS023857', '2021-02-10 17:31:12.000', 8),
('INS023857', '2021-02-11 17:40:20.000', 2),
('INS023857', '2021-02-10 17:31:34.000', 6);
with cte_lead as
(
select id, ts, status
, case when lead(status) over(partition by id order by ts asc) < 8 then lead(ts) over(partition by id order by ts asc) end as ts_end
, case when lead(status) over(partition by id order by ts asc) < 8 then lead(status) over(partition by id order by ts asc) end as status_end
from @MaTable
)
select *
from cte_lead
where status = 8;
id ts status ts_end status_end
--------- --------------------------- ------ --------------------------- ----------
INS023857 2021-02-10 17:31:12.0000000 8 2021-02-10 17:31:34.0000000 6
INS023857 2021-02-10 17:31:45.0000000 8 2021-02-11 17:40:20.0000000 2
INS025067 2021-03-12 03:12:53.0000000 8 2021-03-12 15:19:24.0000000 5 |
Partager