Select the case of comparing two columns

Question

I would like to have a query that uses a larger number of two values ​​/ columns if a different value is true for the record.

I am trying to get accounts in reports. Unfortunately, the database usually stores the Cash value in a column called HoldingQty , while for every other type of holding (stocks, bonds, mutual funds) it stores it in the Qty column.

The problem is that sometimes the value of funds is stored only in Qty , and sometimes in Qty and HoldingQty . Obviously, sometimes it is stored only in HoldingQty , as mentioned above.

Basically, I want my select statement to say: "If security is money, look at qty and hold qty and give me a value of whichever is greater. Otherwise, if security is not money, just give me qty" .

How can I write this in T-SQL? Here are my efforts:

 SELECT h.account_name, h.security_name, h.security_type, h.price, (CASE: WHEN security_type = 'cash' THEN (WHEN h.qty > h.holdingqty THEN h.qty ELSE h.holdingqty) ELSE qty) as quantity, h.total_value FROM holdings h WHERE ........... 

Answer 1

Your request is correct, but you need a little syntax, try below code

  SELECT h.account_name, h.security_name, h.security_type, h.price, CASE WHEN security_type = 'cash' then CASE when h.qty > h.holdingqty then h.qty else h.holdingqty END ELSE qty END AS 'YourColumnName' ) as quantity, h.total_value FROM holdings h where ........... 

Answer 2

Almost there!

 SELECT h.account_name , h.security_name , h.security_type , h.price , CASE WHEN security_type = 'cash' THEN CASE WHEN h.qty > h.holdingqty THEN h.qty ELSE h.holdingqty END ELSE qty END AS quantity , h.total_value FROM holdings h WHERE ........... 

Answer 3

You can achieve this behavior with a nested case expression:

 SELECT h.account_name, h.security_name, h.security_type, h.price, CASE security_type WHEN 'cash' THEN CASE WHEN h.qty > h.holdingqty THEN h.qty ELSE h.holdingqty END ELSE h.qty END FROM holdings h WHERE ...........