CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Rich
Rich

Reputation: 271

Can I use a variable instead of the integer in OVER clause

I have a query that calculates a 50 day moving average from a column Price like this:

select Date,  
       price, 
       avg(price) over( order by Date, Date rows between 50 preceding and current row) as moving_avg 
from t1

Now I would like to replace the integer 50 by a integer variable to do a loop testing different moving average length.

When I try I get:

Incorrect syntax near '@MA'

Upvotes: 3

Views: 988

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

I was hoping that (Select @MA) would work, but alas no luck

Perhaps you can go dynamic

Declare @MA int = 50

Declare @SQL varchar(max) ='Select Date, price, avg(price) over( order by Date, Date rows between '+cast(@MA as varchar(25))+' preceding and current row) as moving_avg from t1 '
Exec(@SQL)

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

Unfortunately not. If you know how to read them, the syntax diagrams included in the SQL documentation are quite thorough.

As you peruse the one for OVER, you'll eventually find that the variant for the PRECEDING specification is <unsigned_value_specification> PRECEDING.

And then lower down:

<unsigned value specification> ::=   
{  <unsigned integer literal> }

So, unfortunately, your only choice at this time is to use a literal - not a variable, not an expression. When variants (variable vs literal, say) are allowed, the syntax diagrams do tend to make such variants explicitly visible.

The comparable syntax for TOP has:

[   
    TOP (expression) [PERCENT]  
    [ WITH TIES ]  
]

where as you already know, you can use any expression here.

Upvotes: 1

Related Questions