Dynamic Row Range When Calculating Moving Sum/average Using Window Functions (sql Server)

I'm currently working on a sample script which allows me to calculate the sum of the previous two rows and the current row. However, I would like to make the number '2' as a variab

Solution 1:

DECLARE@testVARCHAR=1DECLARE@sqlCommandVARCHAR(1000)
DECLARE@myTableTABLE  (myValue INT)

INSERTINTO@myTable ( myValue ) VALUES  ( 5)
INSERTINTO@myTable ( myValue ) VALUES  ( 6)
INSERTINTO@myTable ( myValue ) VALUES  ( 7)
INSERTINTO@myTable ( myValue ) VALUES  ( 8)
INSERTINTO@myTable ( myValue ) VALUES  ( 9)
INSERTINTO@myTable ( myValue ) VALUES  ( 10)

SET@sqlCommand='SELECT SUM(myValue) OVER (ORDER BY myValue ROWS BETWEEN '+@test+' PRECEDING AND CURRENT ROW)
                  FROM #temp'EXEC (@sqlCommand)

Solution 2:

You can try something like this which does not use dynamic SQL.

DECLARE@myTableTABLE  (myValue INT)

INSERTINTO@myTable ( myValue ) VALUES  ( 5)
INSERTINTO@myTable ( myValue ) VALUES  ( 6)
INSERTINTO@myTable ( myValue ) VALUES  ( 7)
INSERTINTO@myTable ( myValue ) VALUES  ( 8)
INSERTINTO@myTable ( myValue ) VALUES  ( 9)
INSERTINTO@myTable ( myValue ) VALUES  ( 10)


DECLARE@prev_records INT=2

;WITH CTE as 
(
SELECTROW_NUMBER() OVER(ORDERBY myValue) rn,myValue FROM@myTable
)
SELECT (SELECTSUM(myValue) FROM CTE t2 WHERE t2.rn BETWEEN (t1.rn  -@prev_records) AND t1.rn )
FROM CTE t1

SUM(myValue) OVER() is best option however it does not allow you to pass previous N rows using a variable.

Solution 3:

If possible dispersion of the range variable is not very high, you can use simple CASE statement to switch between calculations

DECLARE@myTableTABLE  (myValue INT)

-- let's say it could be between 1 and 10DECLARE@rangeint=3;

INSERTINTO@myTable ( myValue ) VALUES  ( 5)
INSERTINTO@myTable ( myValue ) VALUES  ( 6)
INSERTINTO@myTable ( myValue ) VALUES  ( 7)
INSERTINTO@myTable ( myValue ) VALUES  ( 8)
INSERTINTO@myTable ( myValue ) VALUES  ( 9)
INSERTINTO@myTable ( myValue ) VALUES  ( 10)

SELECTCASE@rangeWHEN1THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN1 PRECEDING ANDCURRENTROW)
        WHEN2THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN2 PRECEDING ANDCURRENTROW)
        WHEN3THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN3 PRECEDING ANDCURRENTROW)
        WHEN4THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN4 PRECEDING ANDCURRENTROW)
        WHEN5THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN5 PRECEDING ANDCURRENTROW)
        WHEN6THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN6 PRECEDING ANDCURRENTROW)
        WHEN7THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN7 PRECEDING ANDCURRENTROW)
        WHEN8THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN8 PRECEDING ANDCURRENTROW)
        WHEN9THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN9 PRECEDING ANDCURRENTROW)
        WHEN10THENSUM(myValue) OVER (ORDERBY myValue ROWSBETWEEN10 PRECEDING ANDCURRENTROW)
    ENDFROM@myTable