Calculating Moving Range in SQL Server (without arrays)

Question

Hi, I have a requirement to calculate the Moving Range of a load of data (at least I think this is what it is called) in SQL Server. This would be easy if I could use arrays, but I understand this is not possible for MS SQL, so wonder if anyone had a suggestion.

To give you an idea of what I need:

Lets say I have the following in a sql server table:

1  
3  
2  
6  
3

I need to get the difference of each of these numbers (in order), ie:

|1-3|=2  
|3-2|=1  
|6-2|=4  
|3-6|=3

Then square these:

2^2=4  
1^2=1  
4^2=16  
3^2=9

EDIT: PROBABLY WORTH NOTING THAT YOU DO NOT SQUARE THESE FOR MOVING AVERAGE - I WAS WRONG

Then sum them:

4+1+16+9=30

Then divide by number of values:

30/5=6

Then square root this:

2.5(ish)

EDIT: BECAUSE YOU ARENT SQUARING THEM, YOU ARENT SQROOTING THEM EITHER

If anyone can just help me out with the first step, that would be great - I can do the rest myself.

A few other things to take into account:
- Using stored procedure in SQL Server
- There is quite a lot of data (100s or 1000s of values), and they will need to be calulated daily or weekly

Many thanks in advance.

~Bob

Answer 1

WITH    nums AS
        (
        SELECT  num, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    mytable
        )
SELECT  SQRT(AVG(POWER(tp.num - tf.num, 2)))
FROM    nums tp
JOIN    nums tf
ON      tf.rn = tp.rn + 1