CODEWITHSUNDEEP
sql
Jesus Sobalvarro
Jesus Sobalvarro

Reputation: 5

select rows if all meets same criteria and only one meets a criteria

I have a OrdersDetails Table so i want to get the status of the orders based on orders status, when all products are new then status is new, when all products are closed then status is closed, when some product is pending then status is pending.

OrderId     |ProductID   |ProductStatus
ORDER1      |PRODUCT1    |NEW
ORDER1      |PRODUCT2    |PENDING
ORDER2      |PRODUCT3    |CLOSED
ORDER2      |PRODUCT4    |CLOSED
ORDER3      |PRODUCT5    |NEW
ORDER3      |PRODUCT6    |NEW
ORDER4      |PRODUCT7    |CLOSED
ORDER4      |PRODUCT8    |PENDING

Desired Result Table

OrderID  |Status
ORDER1   |PENDING
ORDER2   |CLOSED
ORDER3   |NEW
ORDER4   |PENDING

Upvotes: 0

Views: 64

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

Assuming you never will get mixed states unless the order is pending, this should work:

select OrderId, 'PENDING' Status
from #orderTable
group by OrderId
having COUNT(distinct ProductStatus)>1
union all
select OrderId, MIN(ProductStatus) Status
from #orderTable
group by OrderId
having COUNT(distinct ProductStatus)=1
order by 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a group by with case:

select orderid,
       (case when min(status) = max(status) and min(status) in ('NEW', 'CLOSED')
             then min(status)
             when sum(case when status = 'PENDING' then 1 else 0 end) > 0
             then 'PENDING'
             else 'This case is not described in the question'
        end)
from t
group by orderid;

Upvotes: 4

Related Questions