SQL Case Statement to Evaluate multiple values in one column-CodePudding

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...

But I'm getting...

CodePudding user response：

The first issue is you need to reorder the WHEN conditions to list Both Orders first. The CASE expression stops evaluating after the first match.

The second issue the Cust.Cust_ID is a single value, and it's not possible to have a single Cust_ID be both 154 and 30400010112 at the same time. But I can't tell you how to fix this, because I can't see the rest of the query or data.

The third issue is there's no FROM clause in the query, so the database doesn't know what table to look at.

CodePudding user response：

you have a typo in your code

WHEN Cust. Order_ID in ('154','3042000103','3042000099') and Cust. Order_ID in ('30400010112','3042000104') THEN 'BothOrders’

'BothOrders’ quotes don't match, instead try 'BothOrders' like:

WHEN Cust. Order_ID in ('154','3042000103','3042000099') and Cust. Order_ID in ('30400010112','3042000104') THEN 'BothOrders'

CodePudding user response：

Making a few assumptions...

with
    cust as (
        select 
            cust_id,
            case when order_id in ('154','3042000103','3042000099') then 1 else 0 end as pre_order,
            case when order_id in ('30400010112','3042000104') then 1 else 0 end as post_order
        from
            Customer
    )
select
    cust_id,
    case 
        when sum(pre_order) > 0 and sum(post_order) > 0 then 'BothOrders'
        when sum(post_order) > 0 then 'PostOrder'
        when sum(pre_order) > 0 then 'PreOrder'
        else null
    end as orders
from 
    cust
group by
    cust_id

CodePudding user response：

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;