Select Case Comparing Two Columns
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...
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 ...........
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
