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

Use a compound case statement `case when security_Type = 'cash' and h.qty<=h.holdingqty then h.holdingqty else h.qty end as quantity` this basically says when type is cash and holdingqty is > or equal to qty use holdingqty, otherwise use qty. since we know when it's not cash we always use qty, i changed the order a bit to simplify the case statement. — xQbert, Sep 30 '15 at 19:54

score 7 · Accepted Answer · answered Sep 30 '15 at 19:54

7

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

answered Sep 30 '15 at 19:54

Anuj Tripathi

2,251
14
18

Thanks for your help! – Mike O. Oct 01 '15 at 21:06

score 2 · Answer 2 · answered Sep 30 '15 at 19:55

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

answered Sep 30 '15 at 19:55

LordBaconPants

1,404
1
19
22

Thanks for your help! – Mike O. Oct 01 '15 at 21:06

score 2 · Answer 3 · answered Sep 30 '15 at 19:58

2

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

answered Sep 30 '15 at 19:58

Mureinik

297,002
52
306
350

Thanks for your help! – Mike O. Oct 01 '15 at 21:06

Select case comparing two columns

3 Answers3