SQL optimization Case statement

sql, sql-optimization, sybase

Solution

You can simplify it to this to use a single query rather than two. You still need the one because a GROUP BY in an UPDATE doesn't work.

UPDATE bi 
SET    payments = bd.payments, 
       charges= bd.charges, 
       balance = bd.balance 
FROM   billing bi 
       LEFT JOIN (SELECT bd.inv, 
                         payments = Round(Sum(CASE 
                                                WHEN code_type IN ( 'AB' , 'CD' ) THEN 
                                                bd.bal 
                                                ELSE 0 
                                              END), 2), 
                         charges = Round(Sum(CASE 
                                               WHEN code_type NOT IN ( 'AB' , 'CD' ) THEN 
                                               bd.bal 
                                               ELSE 0 
                                             END), 2), 
                         balance = Round(Sum(bd.bal), 2) 
                  FROM   bill bd 
                  GROUP  BY bd.inv) bd 
         ON bd.inv = bi.inv

Problem

I believe I can optimize this sql statement by using a case statement for the Left Outer Joins. But I have been having hard time setting up the cases, one for summing up the code types AB,CD and another for All the rest. Appreciate any help or tips you can give me on this. ``` update billing set payments = isnull(bd1.amount, payments) , payments = case when payments is null then 0 else payments end , charges = case when bd2.amount is not null then charges when charges is null then 0 else charges end , balance = round(charges + isnull(bd1.amount, bi.payments), 2) from billing bi left outer join (select inv, round(sum(bd1.bal), 2) amount from "bill" bd1 where code_type = 'AB' or code_type = 'CD' group by inv) bd1 on bd1.inv = bi.inv left outer join (select invoice, round(sum(bd2.bal), 2) amount from "bill" bd2 where code_type <> 'AB' and code_type <> 'CD' group by inv) bd2 on bd2.inv = bi.inv; ```

Original source