There are two fields in my table cust_shipmentdate_awb and comp_shipdate_awb. Iam trying to write a CASE Statement which should check both fields cust_shipmentdate_awb and comp_shipdate_awb is null or empty. If both are Null then show the result as 'Pending'. If any one field is not empty, then it should show as 'Completed' AND if both fields are entered also it should as 'Completed'. I have tried the below CASE statement. But its showing pending when both the fields data is entered. Iam trying to achieve as per the below data.
cust_shipmentdate_awb | comp_shipdate_awb | shipment_status
02-03-2016 | | Completed
| 09-08-2016 | Completed
NULL | NULL | Pending
01-06-2016 | 09-08-2016 | Completed
When cust_shipmentdate_awb Is Null Or comp_shipdate_awb = '' Then 'Pending'
Else 'Completed' End AS shipment_status
Just try to use the
AND operator and check both fields for null and empty (''):
CASE When (cust_shipmentdate_awb Is Null OR cust_shipmentdate_awb = '') AND (comp_shipdate_awb Is Null OR comp_shipdate_awb = '') Then 'Pending' Else 'Completed' End AS shipment_status