Is it possible to add a new column that sums values of existing column in rows meeting condition in another existing column

Other SQL Server Topics
1

For each row n, I would like to add a third column (B) with a value equal to a sum of values in the second column A (only for rows at or below row n) where the value of the first column time is less than the value in row n +10. The first column has values monotonically increasing.
Example array input:
time A
1 5
3 5
7 5
11 5
25 5
Desired array output:
time A B
1 5 15
3 5 15
7 5 10
11 5 5
25 5 5

For the first row, the first column value is 1, so only rows 1,2, and 3 in column 1 have values lower than 1+10. Hence column 2 values (5+5+5) in rows 1,2,3 are summed and placed in the third column of the first row.

For the second row, the first column value is 3, so only rows 2,3, and 4 in column 1 have values lower than 3+10. Hence column 2 values (5+5+5) in rows 2,3 and 4 are summed and placed in the third column of the second row.

For the third row, the first column value is 7, so only rows 3 and 4 in column 1 have values lower than 7+10. Hence column 2 values (5+5) in rows 3 and 4 are summed and placed in the third column of the third row.

The same approach is done for the remaining rows.

2

its possible

you can use
row number partition by your LOGIC then sum them up

general idea

3

You are more likely to get a good answer if you post consumable test data:

CREATE TABLE #t
(
	[time] int NOT NULL
	,A int NOT NULL
);
INSERT INTO #t
VALUES (1, 5)
	,(3, 5)
	,(7, 5)
	,(11, 5)
	,(25, 5);

The easiest way to do this is CROSS APPLY:

SELECT T.[time], T.A, X.B
FROM #t T
	CROSS APPLY
	(
		SELECT SUM(A)
		FROM #t T1
		WHERE T1.[time] >= T.[time]
			AND T1.[time] < T.[time] + 10
	) X (B);
1 Like
4

Hi Ifor,

Thank you for your suggestions!