Get a single row in result based on one columns data

Her is a table

Order_ID  O_Status
8 1
8 2
8 3
8 4
8 5
8 6

 

from the above table I need to get below result set. (one row)

Order_ID O_Status
8

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

(Order_ID INT,

O_Status INT)

 

INSERT INTO @Tab

VALUES (8,1),(8,2),(8,3),(8,4),(8,5),(8,6)

 

SELECT * FROM @Tab

 

select

      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

            cross apply

                        (select count(*) as Tot_Count from @Tab t where t.Order_ID = s.Order_ID)a

            cross apply

                        (select count(*) as Fiv_Count from @Tab t where t.Order_ID = s.Order_ID and O_Status =5)v

group by Order_ID

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s