Skip to content
Advertisement

Sum of Current + Previous X Nth Rows

I am looking to find the sum of the current + the last X Nth Rows. I am able to do this with the following query, however it is not very scalable.

SELECT [id], [amount] + LAG([amount],6) OVER(ORDER BY [id]) + LAG([amount],12) OVER(ORDER BY [id]) + LAG([amount],18) OVER(ORDER BY [id])

If this example, I’m finding the current value of “amount”, plus the last 3 “amounts” split 6 apart:

  • X = 3
  • N = 6

I will be using these within dynamic queries and would prefer not to build such a complex query each time. There could be many “lags” in some of the queries. Is there another way to write this query that would be more scalable?

SOURCE DATA

ID Amount
1 107.35
2 105.41
3 104.63
4 106.7
5 108.7
6 110.21
7 108.8
8 108.91
9 108.5
10 106.66
11 105.2
12 106.5
13 108.27
14 109.72
15 111.53
16 112.8
17 109.03
18 115.31
19 115.56
20 116.85
21 116.08
22 117.61
23 118.31
24 119.25
25 118.45
26 118.43
27 120.16
28 122.5
29 125.57
30 125.65

EXPECTED RESULTS

ID SUM OF LAST 4
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
9 NULL
10 NULL
11 NULL
12 NULL
13 NULL
14 NULL
15 NULL
16 NULL
17 NULL
18 NULL
19 439.98
20 440.89
21 440.74
22 443.77
23 441.24
24 451.27
25 451.08
26 453.91
27 456.27
28 459.57
29 458.11
30 466.71

Advertisement

Answer

At a best guess, it seems like what you want is something like this:

DECLARE @X int = 3,
        @N int = 6;

SELECT YT.ID,
       YT.Amount,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY G.Grp ORDER BY ID) < @X+1 THEN NULL
            ELSE SUM(Amount) OVER (PARTITION BY G.Grp ORDER BY ID
                                   ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
       END
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(ID % @N))G(Grp)
ORDER BY YT.ID;

You’ll note, however, that the 3 is hardcoded in one place, as you cannot use a variable for the ROWS BETWEEN clause. If you need to parametrise this, you’ll need to use dynamic SQL:

DECLARE @X int = 3,
        @N int = 6;

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = CONCAT(N'SELECT YT.ID,', @CRLF,
                  N'       YT.Amount,', @CRLF,
                  N'       CASE WHEN ROW_NUMBER() OVER (PARTITION BY G.Grp ORDER BY ID) < @X+1 THEN NULL', @CRLF,
                  N'            ELSE SUM(Amount) OVER (PARTITION BY G.Grp ORDER BY ID', @CRLF,
                  N'                                   ROWS BETWEEN ',@X,N' PRECEDING AND CURRENT ROW)', @CRLF, --I don't like injecting raw values, but if @X is an int, it is "safe"
                  N'       END', @CRLF,
                  N'FROM dbo.YourTable YT', @CRLF,
                  N'     CROSS APPLY (VALUES(ID % @N))G(Grp)', @CRLF,
                  N'ORDER BY YT.ID;');
PRINT @SQL; --Your best debugging friend

EXEC sys.sp_executesql @SQL, N'@X int, @N int', @X, @N;

db<>fiddle

7 People found this is helpful
Advertisement