CASE Statement MYSQL with Condition

0
votes

I got 2 CASE statements: First CASE statement is as follows:

Case When ((cust_shipmentdate_awb Is Null OR cust_shipmentdate_awb = '') 
AND (comp_shipdate_awb IS NULL OR comp_shipdate_awb = '')) Then 'Pending'
ELSE 'Shipped' End AS shipment_status

The Second CASE statement is as follows:

Case When apbg_bal_pay_amt ='0' Then 'Received'
    Else 'Pending' End AS payment_status

Iam looking to write one more CASE statement named OVERALL_Status. That is basically a combination of both this CASES (shipment_status and payment_status), which means if Shipment status is 'Shipped' AND Payment_Status is 'Received' then Overall_status is 'Completed' else 'Not Completed'. Can anyone please help me on this. I am really struck here. I tried the combination of both the CASES, but not working:

Case When (cust_shipmentdate_awb Is Null OR cust_shipmentdate_awb = '') AND 
(comp_shipdate_awb IS NULL OR comp_shipdate_awb = '') AND (apbg_bal_pay_amt != '0') Then 
'Pending' ELSE 'Completed' End AS overall_status
2
mysqlsql
If posible give the sql fiddle link what you have done. - Bilas Sarker

2 Answers

0
votes

There is two solutions as I see it: 1. You make the current query a subquery and add another CASE statement on the result of the subquery. (the pretty solution as I see it) 2. You add another WHEN to the existing case. Combining the clauses in the first two WHEN clauses to get the result. (probably the most optimized solution)

0
votes

How about this?

(case when (cust_shipmentdate_awb Is Null OR cust_shipmentdate_awb = '')  and
           (comp_shipdate_awb IS NULL OR comp_shipdate_awb = '')
      then 'Not Completed'
      when apbg_bal_pay_amt = '0' Then 'Completed'
      else 'Not Completed'
 end) as overall_status