Multiple criteria inside CASE WHEN statement Here UserFlag, QtyAvailable and OrderStatus are three different columns and Im trying to create a two additional columns named &quot;BackOrder Status&quot; and &quot;Shipped Status&quot; in another view.
for BackOrder Status the condition would be
UserFlag =”On Order”
,UserFlag =”Sold Out”
, UserFlag =”On Order
, Sold Out”
, QtyAvailable=0
, OrderStatus =“Pending Fulfillment“ 
then it's a &quot;BackOrder&quot; ELSE &quot;Not a BackOrder&quot;

also, for Shipped Status the condition would be
Orders Shipped: OrderStatus =”Billed”
                , OrderStatus =”Partially Fulfilled”
                ,OrderStatus =”Pending Billing”
                , OrderStatus =”Pending Billing/ Partially Fulfilled”
 then it's &quot;Shipped&quot; ELSE &quot;Not Shipped&quot;

Please let me know if you come up with the query for this.Thanks in advance.
Attempt 1:
CASE 
  WHEN Userflag = 'On Order' AND QtyAvailable = '0' THEN 'BackOrder' 
  WHEN Userflag = 'Sold Out' AND QtyAvailable = '0' THEN 'BackOrder' 
  WHEN Userflag = 'On Order, Sold Out' AND QtyAvailable = '0' THEN 'BackOrder' 
  ELSE 'Not a Backorder' 
END

Attempt 2:
CASE 
  WHEN OrderStatus = 'Billed' THEN 'Order Shipped' 
  WHEN OrderStatus = 'Partially Fulfilled' THEN 'Order Shipped' 
  WHEN OrderStatus = 'Pending Billing' THEN 'Order Shipped' 
  WHEN OrderStatus = 'Pending Billing/Partially Fulfilled' THEN 'Order Shipped' 
  ELSE 'Not Shipped' 
END
You can use following:


CREATE tABLE test(UserFlag varchar(100),QtyAvailable INT,OrderStatus VARCHAR(100))
GO




SELECT
CASE WHEN
UserFlag IN ( 'On Order', 'Sold Out', 'On Order,Sold Out')
AND 
QtyAvailable = 0
AND 
OrderStatus = 'Pending Fulfillment' 
then 'BackOrder'
ELSE 'Not a BackOrder' 
END AS [Orders Backorder]
,
 CASE WHEN 
      OrderStatus in ('Billed','Partially Fulfilled','Pending Billing','Pending Billing/ Partially Fulfilled')
 THEN 'Shipped' ELSE 'Not Shipped' END AS [Orders Shipped]
FROM test;
GO


Orders Backorder | Orders Shipped
:--------------- | :-------------


db&lt;&gt;fiddle here