CODEWITHSUNDEEP
sql-server
user3507767
user3507767

Reputation: 87

LAG function in Alter Table produces windowed function error

The following code works fine Select date_trans,time_trans,price, price - lag(price,1,0) over(order by date_tans ASC, time_trans ASC) As pChng From dbo.A_1

But if I try to alter the table and add a column with similar code I get an error. It seams to me the windowed function is inside an order by clause, so I don't understand why the error

Alter table dbo.A_1 Add pChng as price - lag(price,1,0) over(order by date_trans ASC, time_trans ASC)

Upvotes: 1

Views: 91

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

You can't add that as a computed column.

You could either

  • Add a scalar UDF that accepts parameters for date_trans and time_trans and then returns the price of the preceding row and reference that in a computed column.
  • Or just create a view with your lag column.
  • Or use triggers to maintain the pChng column yourself.

The three options have various trade offs between efficiency and implementation complexity. The first one will likely be much less efficient than the second if doing a query against the whole table for example.

Upvotes: 4

Related Questions