SQL Transaction Optimization

Question

I believe I can optimize this sql statement using the case statement for Left Outer Joins.

But I had difficulty setting up things, one for summing up code like AB, CD, and the other for everyone else.

Appreciate any help or advice you can give me.

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; 

Answer 1

You can simplify this by using one query rather than two. You still need one because GROUP BY in UPDATE does not 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 

Answer 2

Maybe something like this:

 update billing set payments = isnull(bd1.amount, payments) , payments = isnull(payments, 0) , charges = isnull(bd2.amount, isnull(charges, 0)) , 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 in ('AB', '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 not in ('AB', 'CD') group by inv) bd2 on bd2.inv = bi.inv; 

Two left connections are not a problem!