Kindly advise , How to create query in sqlserver for below issue,

  • Dear Team ,

    Kindly advise , How to create query in sqlserver for below issue,

    I Have Table with below details

    Id Name Value1 value2 Total(Value1+Value2) PreviousRowId

    1 A 10 0 10 0

    2 B 5 1

    3 C 6 2

    4 D 7 3

    5 E 8 4

    6 F 5 5

    Qustions :

    1 . 1st row Total comes to second row Value1

    then calculate 2nd row total . then its comes to 3rd row Value1 column. and its continue for all rows.

    how to write the query for this issue?

    Note : Link between all rows ==> Id , PreviousRowId

    I need output like this below ,

    Id Name Value1 value2 Total(Value1+Value2) PreviousRowId

    1 A 10 0 10 0

    2 B 10 5 15 1

    3 C 15 6 21 2

    4 D 21 7 28 3

    5 E 28 8 36 4

    6 F 36 5 41 5

    Thanks,

    Chandrahasan S

  • Please visit the following link to see how to best provide data for your questions:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    --------
    For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • You will need a CTE to achieve the results

    DECLARE@table TABLE

    (

    IdINT,

    NameVARCHAR(10),

    Value1INT,

    value2INT,

    PreviousRowId INT

    )

    INSERT@table

    SELECT1, 'A', 10, 0, 0 UNION ALL

    SELECT2, 'B', 0, 5, 1 UNION ALL

    SELECT3, 'C', 0, 6, 2 UNION ALL

    SELECT4, 'D', 0, 7, 3 UNION ALL

    SELECT5, 'E', 0, 8, 4 UNION ALL

    SELECT6, 'F', 0, 5, 5

    ; WITH cte_table AS

    (

    SELECTt.Id, t.Name, t.Value1, t.value2, t.Value1 + t.value2 AS total, t.PreviousRowId

    FROM@table AS t

    WHEREt.PreviousRowId = 0

    UNION ALL

    SELECTt2.Id, t2.Name, ct.total, t2.value2, ct.total + t2.value2 AS total, t2.PreviousRowId

    FROM@table AS t2

    INNER JOIN cte_table AS ct ON t2.PreviousRowId = ct.Id

    )

    SELECT*

    FROMcte_table


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply