Conditional preceding values

Question

My sample data is like this

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) 

I need TSQL to return immediately preceding rowNum for each id with 'status2' where the status='status1'. I hope the code to return this

id	status	rowNum	value
id100	status1	1
id100	status2	2	1
id100	status1	3
id100	status0	4
id100	status2	5	3
id100	status2	6	3
id100	status1	7
id100	status1	8
id100	status2	9	8
id101	status1	10
id101	status2	11	10

I tried this which did not work

SELECT 
  t1.id, 
  t1.status, 
  t1.rowNum, 
  (
    select 
      MIN(t2.rowNum) 
    from 
      #temp t2 
    where 
      t2.id = t1.id 
      and t2.rowNum < t1.rowNum 
      and t1.status = 'status2'
  ) as test 
from 
  #temp t1

Answer 1

You can use subquery within a case statement to do so:

Query:

  select *, CASE WHEN status='status2' then (select max(rowNum) from #temp tmp 
    where tmp.rowNum<t.rowNum and tmp.status='status1') end value
  from #temp t
order by rowNum

Output:

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

fiddle

You can also use last_value() window function or lag() window function instead of subquery:

Query (with last_value()over()):

 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

Output:

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

Query (with lag()over()):

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

Output:

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

fiddle