Sequence Number Generation

• ch.ds19

  SSC Veteran

  Points: 224

  More actions

  October 28, 2010 at 4:56 am

  #147399

  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.

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  October 28, 2010 at 6:00 am

  #1243471

  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