Her is a table
from the above table I need to get below result set. (one row)
the O_Status in the result set is based on different conditions.
1. If 5 is there along with other status values then O_Status is 2. (for my application I need 2)
2. If all O_Status are 5 then 5.
3. If 5 is not there in O_Status then Max(O_Status)
The required query is here.
DECLARE @Tab AS TABLE
INSERT INTO @Tab
SELECT * FROM @Tab
Order_ID, case when (max(Fiv_Count) <> 0 and max(Tot_Count) <> max(Fiv_Count)) then ‘2’ else max(O_Status)end as O_Status
from @Tab s
(select count(*) as Tot_Count from @Tab t where t.Order_ID = s.Order_ID)a
(select count(*) as Fiv_Count from @Tab t where t.Order_ID = s.Order_ID and O_Status =5)v
group by Order_ID