Haggan Haggan - 2 months ago 19
SQL Question

SQL 2008 - Call for named formula

So lets say I have this code:

Select
'Position Date' = todaypositiondate
,'Realized' = round(sum(realizedccy*spotsek),0)

FROM T1
group by todaypositiondate
order by todaypositiondate desc


If I wan to divide 'Realized' with 100.
How can I set
'new' = 'Realized'/100
instead of
'new' = (round(sum(realizedccy*spotsek),0))/100
?

Best regards

Answer

Not sure why you want to do that but here is one way using a CTE. You can also use subqueries, but both of these steps are pretty pointless for the math you want to do, and will decrease performance. You can't reference a column alias by it's alias within the same statement like you are trying to do. This includes referencing it in the where or group by or order by clauses

;with cte as(
Select
    todaypositiondate as 
    round(sum(realizedccy*spotsek),0) as Realized
from T1 
    group by todaypositiondate
    order by todaypositiondate desc)

select 
    todaypositiondate,
    Realized,
    Realized / 100 as RealizedDivHundred
from cte