October 28, 2010 at 4:56 am
Hi all,
I have a column1 with values 1,2,3,4,5 and I want to add cumelatively the values into the target column as 1,3,6,10,15 , using ssis.
can anybody helpme out....
thanks in advance.
October 28, 2010 at 6:00 am
There is a standard mathematical expression for the sum of all integers from 1 to N.
Sum(R) {R = 1..N} = N * (N + 1) / 2
So you could just adapt this query in T-SQL for your purposes.
You will need a Tally (otherwise known as a Numbers table) to generate the sequence of consecutive integers.
SELECT T.N, T.N * (T.N + 1) / 2 AS SumOfN
FROM Tally T
WHERE (T.N BETWEEN 1 AND 10)
N SumOfN
----------- -----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
EDIT: Just noticed you stated that you already had a column (named column1) containing consecutive integers. If so, and if this column is of integer type, then this query will probaly give you what you want.
SELECT column1, column1 * (column1 + 1) / 2 AS SumOfN
FROM MyTable
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply