Sequence Number Generation

  • 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.

  • 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