Member Avatar for Stuugie

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
  • 1K Views
  • 12 Minutes Discussion Span
  • Latest Post Latest Post by Stuugie
Member Avatar for Stuugie

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.