Removing duplicate code from an SQL query -

i have created sql query works expected uses lot of repeated code in select statement calculate value.

for instance in sql statement line:

sum(od.number01) on (order od.number01) 

is repeated several select conditions within different formula.

e.g.

(rd.ourqty - sum(od.number01) on (order od.number01) + od.number01) 'left in stock' 

and

case when (rd.ourqty - sum(od.number01)  on (order od.number01) + od.number01) - od.number01 > 0 od.number01 else (case when (rd.ourqty - sum(od.number01) on (order od.number01)  + od.number01) > 0 (rd.ourqty - sum(od.number01) on (order od.number01) + od.number01) else 0 end) end 'allocated' 

for small datasets isn't of problem. problem arises when dataset becomes larger.

how can store result statement once per row calculation, , use result stored in local variable throughout select statement in required calculations, rather performing same calculation several times?

i using microsoft sql server management studio 2012

why not make use of cte define sum, , use afterwards?

a common table expression (cte) can thought of temporary result set defined within execution scope of single select, insert, update, delete, or create view statement. cte similar derived table in not stored object , lasts duration of query. unlike derived table, cte can self-referencing , can referenced multiple times in same query.

something like

with vals (     select  *,             sum(number01) over(order number01) mysum        mytable ) select *,         mysum val 

sql fiddle demo