0

Hi All,

I have the following statement which is intended to have a Year over Year Percentage change:

select CurrYear.Variable_Date,CurrYear.Variable_Value,cast(100*(CurrYear.Variable_Value-PrevYear.Variable_Value)/PrevYear.Variable_Value) AS Percentage_Change
from dbo.tblCPISC as CurrYear 
left outer join dbo.tblCPISC as PrevYear on year(PrevYear.Variable_Date) =
(select MAX(Variable_Value) from tblCPISC where year(Variable_Date) < year(CurrYear.Variable_Date))
order by year(CurrYear.Variable_Date)

For some reason, I get the following message:

Msg 1035, Level 15, State 10, Line 1
Incorrect syntax near 'cast', expected 'AS'.

Clearly the expected AS is there but clearly I am doing something wrong. Can someone spot my error?

1
Contributor
1
Reply
5
Views
4 Years
Discussion Span
Last Post by Stuugie
0

Alright so I fixed this with AS Decimal(18,2) but now the query just hangs in execute for some reason.

select CurrYear.Variable_Date,CurrYear.Variable_Value,CAST(100*(CurrYear.Variable_Value-PrevYear.Variable_Value)/PrevYear.Variable_Value as decimal(18,2)) AS [Percentage_Change]
from dbo.tblCPISC as CurrYear 
left outer join dbo.tblCPISC as PrevYear on year(PrevYear.Variable_Date) =
(select MAX(year(Variable_Date)) from tblCPISC where year(Variable_Date) < year(CurrYear.Variable_Date))
order by year(CurrYear.Variable_Date)

Edited by Stuugie

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.