SQLTeam.com | Weblogs | Forums

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

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.

its possible

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

general idea

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

Hi Ifor,

Thank you for your suggestions!