Menu
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 variable. I've tried declaring a variable, or directly casting in the query, yet a syntax error always pops up. Is there a possible solution?
DECLARE @myTable TABLE (myValue INT)
INSERT INTO @myTable ( myValue ) VALUES ( 5)
INSERT INTO @myTable ( myValue ) VALUES ( 6)
INSERT INTO @myTable ( myValue ) VALUES ( 7)
INSERT INTO @myTable ( myValue ) VALUES ( 8)
INSERT INTO @myTable ( myValue ) VALUES ( 9)
INSERT INTO @myTable ( myValue ) VALUES ( 10)
SELECT
SUM(myValue) OVER (ORDER BY myValue
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM @myTable
270
You can do this by using ROW_NUMBER () function provided by Sql server. your table name. It will retrieve the records from rows 10 to 20 from your table.
UNBOUNDED PRECEDING – All rows before the current row. n PRECEDING – n rows before the current row. CURRENT ROW – Just the current row. n FOLLOWING – n rows after the current row. UNBOUNDED FOLLOWING – All rows after the current row.
The frame, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, means that the window consists of the first row of the partition and all the rows up to the current row. Each calculation is done over a different set of rows. For example, when performing the calculation for row 4, the rows 1 to 4 are used.
Introduction to SQL Window Functions Similar to an aggregate function, a window function calculates on a set of rows. However, a window function does not cause rows to become grouped into a single output row. In this example, the OVER() clause signals that the SUM() function is used as a window function.
DECLARE @test VARCHAR = 1
DECLARE @sqlCommand VARCHAR(1000)
DECLARE @myTable TABLE (myValue INT)
INSERT INTO @myTable ( myValue ) VALUES ( 5)
INSERT INTO @myTable ( myValue ) VALUES ( 6)
INSERT INTO @myTable ( myValue ) VALUES ( 7)
INSERT INTO @myTable ( myValue ) VALUES ( 8)
INSERT INTO @myTable ( myValue ) VALUES ( 9)
INSERT INTO @myTable ( myValue ) VALUES ( 10)
SET @sqlCommand = 'SELECT SUM(myValue) OVER (ORDER BY myValue ROWS BETWEEN ' + @test + ' PRECEDING AND CURRENT ROW)
FROM #temp'
EXEC (@sqlCommand)
answered Sep 22 '22 21:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With