CODEWITHSUNDEEP
sqlsql-servert-sql
Kanan Mehdizade
Kanan Mehdizade

Reputation: 73

Taking the last change value

I need to find the date when the hit = 1, and when the hit moved from 1 to 0.Here is the table

enter image description here

Once I use this code. For the following table 

 select t1.id,t1.hit,min(datehit_1) as Hit_1, min(dateHit_0)as modelHit_0 from (
 select 
 id, 
 dateHit_0 = (case when hits = 0 then date else null end),
 datehit_1 = (case when hits = 1 then date else null end), 
 datacontrol_modelhits
 from dmt.dqm_std_model_output_history
 where id = 10 ) as t1 
 group by t1.id, t1.hit

But Unfortunately, I am getting the this output:

enter image description here

But I need to get this output. Want to find the last change for hit =0 which is 23/10/2019.

enter image description here

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I need to find the date when the hit = 1, and when the hit moved from 1 to 0.Here is the table

I think you want:

select t.*
from (select t.*,
             lag(hit) over (partition by id order by coalesce(datehit_0, datehit_1) as prev_hit,
             lead(hit) over (partition by id order by coalesce(datehit_0, datehit_1) as next_hit
      from t
     ) t
where (hit = 1 and next_hit = 0) or
      (hit = 0 and prev_hit = 1);

If you want only the most recent two rows:

select top (2) t.*
from (select t.*,
             lag(hit) over (partition by id order by coalesce(datehit_0, datehit_1) as prev_hit,
             lead(hit) over (partition by id order by coalesce(datehit_0, datehit_1) as next_hit
      from t
     ) t
where (hit = 1 and next_hit = 0) or
      (hit = 0 and prev_hit = 1)
order by coalesce(datehit_0, datehit_1) desc;

Upvotes: 2

Related Questions