Select case comparing two columns

Question

I would like to have a query that uses the greater of two values/columns if a certain other value for the record is true.

I'm trying to get a report account holdings. Unfortunately the DB usually stores the value of Cash in a column called HoldingQty, while for every other type of holding (stocks, bonds, mutual funds) it stores it in a column called Qty.

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

Basically I want my select statement to say "if the security is cash, look at both qty and holding qty and give me the value of whatever is greater. Otherwise, if the security isn't cash just give me qty".

How would I write that in T-SQL? Here is my effort:

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 query is correct but need few syntax arrangement, 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 ...........