Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequence Number Generation Expand / Collapse
Author
Message
Posted Thursday, October 28, 2010 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 12:52 AM
Points: 8, Visits: 132
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.
Post #1012200
Posted Thursday, October 28, 2010 6:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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



Post #1012235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse