CODEWITHSUNDEEP
sqlsql-server
Rahul Kumar
Rahul Kumar

Reputation: 524

Identify the pattern to get desired output

We have below table contains multiple code against the ID and CounterID. Need an output based on code and id.

Condition: Against the ID and CounterID

case 1 : if there is code CI and CO then no record

case 2 : if there is code CI and CO and CI then last record with code CI

case 3 : if there is code CI then last CI

enter image description here

Thanks Rahul

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Your logic simplifies to: The last row when it has 'CI'. One method with window functions is:

select t.*
from (select t.*, 
             row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
where seqnum = 1 and code = 'CI';

You can also do this without window functions:

select t.*
from t
where code = 'CI' and
      date = (select max(t2.date) from t t2 where t2.id = t.id);

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use the analytical function as follows:

select t.* from
(select t.*, row_number() over (partition by sno order by date desc) as rn,
       count(case when code = 'CI' then 1 end) over (partition by sno) as cicount,
       count(case when code = 'CO' then 1 end) over (partition by sno) as cocount 
from your_table t) t
where ( (cocount = 0)
       or not (cicount = 1 and cocount  = 1) 
       or (cocount > cicount ))
  and rn = 1

Upvotes: 1

Related Questions