sql - How can I compute values in a TSQL Update statement? -

i want create 3 temp tables , combine contents so:

create table #temp1 memberitemcode varchar(25), week1usage varchar(25), week1price varchar(25);  insert #temp1 (memberitemcode, week1usage, week1price) select memberitemcode, sum(qtyshipped), price  invoicedetail  unit=@unit , invoicedate between @begdate , @week1end group memberitemcode, price  create table #temp2 memberitemcode varchar(25), week2usage varchar(25), week2price varchar(25);  insert #temp2 (memberitemcode, week2usage, week2price) select memberitemcode, sum(qtyshipped), price  invoicedetail  unit=@unit , invoicedate between @week2begin , @enddate group memberitemcode, price  create table #temp3 memberitemcode varchar(25), description varchar(200), varchar(25), varchar(25), theotherthing varchar(25);  insert #temp3 (memberitemcode, description, this, that, theotherthing) select memberitemcode, description, this, that, theotherthing  invoicedetail  unit=@unit , invoicedate between @begdate , @enddate  create table #tempcombined memberitemcode varchar(25), description varchar(200), varchar(25), varchar(25), theotherthing varchar(25), week1usage varchar(25), week1price varchar(25); week2usage varchar(25), week2price varchar(25), usagevariance varchar(25), pricevariance varchar(25), pricevariancepercentage varchar(25);  insert #tempcombined (memberitemcode, description, this, that, theotherthing, week1usage, week1price, week2usage, week2price, usagevariance, pricevariance, pricevariancepercentage) select t1.memberitemcode, t3.description, t3.this, t3.that, t3.theotherthing, t1.week1usage, t1.week1price, t2.week2usage, t2.week2price, null,  null,  null #temp1 t1 left join #temp2 t2 on t1.memberitemcode = t2.memberitemcode left join #temp3 t3 on t1.memberitemcode = t3.memberitemcode 

now want replace placeholder nulls in calculated fields, don't know how that. best idea far is:

update #tempcombined set usagevariance = week2usage - week1usage, pricevariance = week2price - week1price, pricevariancepercentage = (week2price - week1price) / week1price 

is there better way (one works, instance)?

using computed columns:

create table #tempcombined(   memberitemcode varchar(25),   description varchar(200),   varchar(25),   varchar(25),   theotherthing varchar(25),   week1usage decimal(18,10),   week1price decimal(18,10),   week2usage decimal(18,10),   week2price decimal(18,10),   usagevariance   week2usage - week1usage,   pricevariance   week2price - week1price,   pricevariancepercentage  (week2price - week1price) / week1price ); 

livedemo

and skip them in insert statement:

insert #tempcombined (memberitemcode, description, this, that, theotherthing, week1usage, week1price, week2usage, week2price) select t1.memberitemcode, t3.description, t3.this, t3.that, t3.theotherthing, t1.week1usage, t1.week1price, t2.week2usage, t2.week2price #temp1 t1 left join #temp2 t2 on t1.memberitemcode = t2.memberitemcode left join #temp3 t3 on t1.memberitemcode = t3.memberitemcode 

warning:

division week1price add (week2price - week1price) / nullif(week1price,0) avoid math exception or add check constraint ensure week1price not 0.