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 ........... 3 Answers
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 ........... 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 ........... 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 ........... 
