By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop
table if exists #temp
select
* into #temp
from
(
values
('id100', 'status1', 1),
('id100', 'status2', 2),
('id100', 'status1', 3),
('id100', 'status0', 4),
('id100', 'status2', 5),
('id100', 'status2', 6),
('id100', 'status1', 7),
('id100', 'status1', 8),
('id100', 'status2', 9),
('id101', 'status1', 10),
('id101', 'status2', 11)
) t(id, status, rowNum)
11 rows affected
select *, CASE WHEN status='status2' then
(last_value(case when status='status1' then rowNum else null end) ignore nulls over(order by rowNum)) end value
from #temp t
order by rowNum
| id | status | rowNum | value |
|---|---|---|---|
| id100 | status1 | 1 | null |
| id100 | status2 | 2 | 1 |
| id100 | status1 | 3 | null |
| id100 | status0 | 4 | null |
| id100 | status2 | 5 | 3 |
| id100 | status2 | 6 | 3 |
| id100 | status1 | 7 | null |
| id100 | status1 | 8 | null |
| id100 | status2 | 9 | 8 |
| id101 | status1 | 10 | null |
| id101 | status2 | 11 | 10 |
select *, CASE WHEN status='status2' then
(lag(case when status='status1' then rowNum else null end) ignore nulls over(order by rowNum)) end value
from #temp t
order by rowNum
| id | status | rowNum | value |
|---|---|---|---|
| id100 | status1 | 1 | null |
| id100 | status2 | 2 | 1 |
| id100 | status1 | 3 | null |
| id100 | status0 | 4 | null |
| id100 | status2 | 5 | 3 |
| id100 | status2 | 6 | 3 |
| id100 | status1 | 7 | null |
| id100 | status1 | 8 | null |
| id100 | status2 | 9 | 8 |
| id101 | status1 | 10 | null |
| id101 | status2 | 11 | 10 |