Search code examples
sqlcase

SQL Case Statement to Evaluate multiple values in one column

I have a table that store the customer's 'PreOrder' number and 'PostOrder' number in the same column. I'm trying to write a case statement that will evaluate if the patient has one of the 'PreOrder' number and/or the 'PostOrder' number. There are three different numbers that could represent a 'PreOrder' and two different numbers that will represent a 'PostOrder'. If the customer has one of the 'PreOrder' numbers, I will like to return 'Pre'. If the customer has one of the 'PostOrder' numbers, I will like to return 'Post'. If a customer has one of the 'PreOrder' numbers and one of the 'PostOrder' numbers, I will like to return 'BothOrders'. Here is my query...

SELECT 
   Cust.Cust_ID 
 , CASE WHEN Cust.Order_ID in ('154','3042000103','3042000099') THEN 'PreOrder'
        WHEN Cust. Order_ID in ('30400010112','3042000104') THEN 'PostOrder'
        WHEN Cust. Order_ID in ('154','3042000103','3042000099') and Cust. Order_ID in ('30400010112','3042000104') THEN 'BothOrders'
END as Orders
From Customer as Cust
GROUP BY Cust.Cust_ID

This is not working because it's not returning 'BothOrders' for customers who has either of the 'PreOrder' and either of the 'PostOrder' numbers. The
values in Order's column is (154,3042000103, 3042000099, 30400010112, 3042000099).

I'm expecting something like this... enter image description here

But I'm getting... enter image description here

Solution

  • You need to determine if each customer has a unique group of order types.

    You can't evaluate this with a simple case expression, even if the order of the evaluation was correct, since you need to evaluate all a customer's rows; another way you can approach this is to first define your two possible groups and then evaluate if they are the same for a customer, such as:

    with t as (
  select *, 
    case when order_id in ('30400010112','3042000104')  then 'Post' else 'Pre' end type
  from Customer 
)
select distinct CustId, 
  case when min(type) over(partition by CustId)
       = max(type) over(partition by CustId) then type else 'Both' 
  end as Orders
from t;