Post reply

Need help in a query - Accessing and modifying previous row data

  • August 22, 2008 at 12:38 am

    #94501

    create table #Temps

    (

    num int, OP_Bal int, quantity int

    )

    insert into #Temps values(1, 234, 12)

    insert into #Temps values(2, 234, 08)

    insert into #Temps values(3, 234, 9)

    insert into #Temps values(4, 234, 11)

    select * from #Temps

    I need a result something like this

    num OP_Bal quantity Closing_Bal

    -----------------------------------------------------

    1 234 12 246

    2 246 8 254

    3 254 9 263

    4 264 11 275

    Formula: Closing Balance = (OP_Bal + quantity) .

    Closing Balance of firstrow will become opening balance of next row.

    Please send me a query for this. I think CTE will help.

  • August 22, 2008 at 1:22 am

    #861680

    Hi,

    Please find the below code:

    WITH C_TEMP AS

    (

    SELECT T.num,T.OP_Bal,T.quantity,T.OP_Bal + T.quantity AS Closing_Bal

    FROM #Temps T

    WHERE T.num = 1

    UNION ALL

    SELECT T1.num,C.Closing_Bal,T1.quantity,C.Closing_Bal + T1.quantity AS Closing_Bal

    FROM #Temps T1

    INNER JOIN C_TEMP C ON T1.num=C.num+1

    )

    SELECT * FROM C_TEMP

    ORDER BY 1

    Thanks,

    Amit Khanna

  • August 22, 2008 at 1:31 am

    #861681

    sg (8/22/2008)

    create table #Temps

    (

    num int, OP_Bal int, quantity int

    )

    insert into #Temps values(1, 234, 12)

    insert into #Temps values(2, 234, 08)

    insert into #Temps values(3, 234, 9)

    insert into #Temps values(4, 234, 11)

    select * from #Temps

    I need a result something like this

    num OP_Bal quantity Closing_Bal

    -----------------------------------------------------

    1 234 12 246

    2 246 8 254

    3 254 9 263

    4 264 11 275

    Formula: Closing Balance = (OP_Bal + quantity) .

    Closing Balance of firstrow will become opening balance of next row.

    Please send me a query for this. I think CTE will help.

    There you go:

    create table #Temps

    (

    num int, OP_Bal int, quantity int, CL_Bal int

    )

    insert into #Temps values(1, 234, 12,0)

    insert into #Temps values(2, 234, 08,0)

    insert into #Temps values(3, 234, 9,0)

    insert into #Temps values(4, 234, 11,0)

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    drop table #Temps

  • August 22, 2008 at 1:32 am

    #861682

    nice solution amit.

    "Keep Trying"

  • August 22, 2008 at 1:35 am

    #861684

    arjun.tewari (8/22/2008)

    sg (8/22/2008)

    create table #Temps

    (

    num int, OP_Bal int, quantity int

    )

    insert into #Temps values(1, 234, 12)

    insert into #Temps values(2, 234, 08)

    insert into #Temps values(3, 234, 9)

    insert into #Temps values(4, 234, 11)

    select * from #Temps

    I need a result something like this

    num OP_Bal quantity Closing_Bal

    -----------------------------------------------------

    1 234 12 246

    2 246 8 254

    3 254 9 263

    4 264 11 275

    Formula: Closing Balance = (OP_Bal + quantity) .

    Closing Balance of firstrow will become opening balance of next row.

    Please send me a query for this. I think CTE will help.

    There you go:

    create table #Temps

    (

    num int, OP_Bal int, quantity int, CL_Bal int

    )

    insert into #Temps values(1, 234, 12,0)

    insert into #Temps values(2, 234, 08,0)

    insert into #Temps values(3, 234, 9,0)

    insert into #Temps values(4, 234, 11,0)

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    drop table #Temps

    This CTE will be highly help ful in your case as you have sequential IDs. If in your environment the IDs are not sequential, use my query.

  • August 22, 2008 at 2:00 am

    #861691

    Thanks chirag for the compliment. 🙂

    yes arjun you are right..

  • August 22, 2008 at 2:23 am

    #861699

    arjun.tewari (8/22/2008)

    arjun.tewari (8/22/2008)

    sg (8/22/2008)

    create table #Temps

    (

    num int, OP_Bal int, quantity int

    )

    insert into #Temps values(1, 234, 12)

    insert into #Temps values(2, 234, 08)

    insert into #Temps values(3, 234, 9)

    insert into #Temps values(4, 234, 11)

    select * from #Temps

    I need a result something like this

    num OP_Bal quantity Closing_Bal

    -----------------------------------------------------

    1 234 12 246

    2 246 8 254

    3 254 9 263

    4 264 11 275

    Formula: Closing Balance = (OP_Bal + quantity) .

    Closing Balance of firstrow will become opening balance of next row.

    Please send me a query for this. I think CTE will help.

    There you go:

    create table #Temps

    (

    num int, OP_Bal int, quantity int, CL_Bal int

    )

    insert into #Temps values(1, 234, 12,0)

    insert into #Temps values(2, 234, 08,0)

    insert into #Temps values(3, 234, 9,0)

    insert into #Temps values(4, 234, 11,0)

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    drop table #Temps

    This CTE will be highly help ful in your case as you have sequential IDs. If in your environment the IDs are not sequential, use my query.

    Thats right. but you have added a new column. In a real world scenario this may not be possible. Nice solution none the less.

    "Keep Trying"

  • August 22, 2008 at 3:14 am

    #861721

    Chirag (8/22/2008)

    arjun.tewari (8/22/2008)

    arjun.tewari (8/22/2008)

    sg (8/22/2008)

    create table #Temps

    (

    num int, OP_Bal int, quantity int

    )

    insert into #Temps values(1, 234, 12)

    insert into #Temps values(2, 234, 08)

    insert into #Temps values(3, 234, 9)

    insert into #Temps values(4, 234, 11)

    select * from #Temps

    I need a result something like this

    num OP_Bal quantity Closing_Bal

    -----------------------------------------------------

    1 234 12 246

    2 246 8 254

    3 254 9 263

    4 264 11 275

    Formula: Closing Balance = (OP_Bal + quantity) .

    Closing Balance of firstrow will become opening balance of next row.

    Please send me a query for this. I think CTE will help.

    There you go:

    create table #Temps

    (

    num int, OP_Bal int, quantity int, CL_Bal int

    )

    insert into #Temps values(1, 234, 12,0)

    insert into #Temps values(2, 234, 08,0)

    insert into #Temps values(3, 234, 9,0)

    insert into #Temps values(4, 234, 11,0)

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    drop table #Temps

    This CTE will be highly help ful in your case as you have sequential IDs. If in your environment the IDs are not sequential, use my query.

    Thats right. but you have added a new column. In a real world scenario this may not be possible. Nice solution none the less.

    You dont need to physically add a column to the base table, you can any ways work on the table variables. 🙂

  • August 22, 2008 at 3:30 am

    #861727

    Thanks a lot Amit Khanna.. 🙂 Great job..

    Updating the temp table as done by Arjun tewari will not be a good idea in this case..

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    Since OP_Bal value is not static. Thank you for your try arjun, but your solution is not the part of my requirement.

  • August 22, 2008 at 3:40 am

    #861734

    sg (8/22/2008)

    Thanks a lot Amit Khanna.. 🙂 Great job..

    Updating the temp table as done by Arjun tewari wont work

    declare @i int

    set @i=234

    update #temps set @i = cl_bal = @i + quantity

    update #temps set op_bal = cl_bal - quantity

    select * from #Temps

    Since OP_Bal value is not static. Thank you for your try arjun, but your solution is not the part of my requirement.

    select @i=min(op_bal) from #temps

    I thaught, its pretty clear :hehe:. and its never about working or not working, everyone posting a solution will work but its always about finding a solution which IS MOST PERFORMANT.

  • August 23, 2008 at 11:48 pm

    #862273

    Hi Amit,

    Can you (or anybody) explain me how this CTE works..??

    I am confused.. 🙁

    Thanks in advance..

  • August 24, 2008 at 10:00 am

    #862297

    arjun.tewari (8/22/2008)

    and its never about working or not working, everyone posting a solution will work but its always about finding a solution which IS MOST PERFORMANT.

    Hey guys... Arjun is trying to save your computational life... if you end up with a million rows of data to do this to, all the other methods shown so far will suffer pretty badly in the performance area. Please see the article at the following link for proof and some reasons why... it also shows how to get around the problem of "adding a column".

    http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 25, 2008 at 12:26 am

    #862386

    Hi Sg,

    WITH C_TEMP AS

    (

    SELECT T.num,T.OP_Bal,T.quantity,T.OP_Bal + T.quantity AS Closing_Bal

    FROM #Temps T

    WHERE T.num = 1

    UNION ALL

    SELECT T1.num,C.Closing_Bal,T1.quantity,C.Closing_Bal + T1.quantity AS Closing_Bal

    FROM #Temps T1

    INNER JOIN C_TEMP C ON T1.num=C.num+1

    )

    SELECT * FROM C_TEMP

    ORDER BY 1

    This is a recursive CTE. It will first pick up the first record, then the Closing_Bal calculated in first select will be the opening balance of second select statement. Note the C_TEMP in the join clause based on T1.num=C.num+1 condition.

    Please let me know if you need further explanation.

    Thanks,

    Amit Khanna

  • August 25, 2008 at 9:23 pm

    #862885

    Amit Khanna (8/25/2008)

    Hi Sg,

    WITH C_TEMP AS

    (

    SELECT T.num,T.OP_Bal,T.quantity,T.OP_Bal + T.quantity AS Closing_Bal

    FROM #Temps T

    WHERE T.num = 1

    UNION ALL

    SELECT T1.num,C.Closing_Bal,T1.quantity,C.Closing_Bal + T1.quantity AS Closing_Bal

    FROM #Temps T1

    INNER JOIN C_TEMP C ON T1.num=C.num+1

    )

    SELECT * FROM C_TEMP

    ORDER BY 1

    This is a recursive CTE. It will first pick up the first record, then the Closing_Bal calculated in first select will be the opening balance of second select statement. Note the C_TEMP in the join clause based on T1.num=C.num+1 condition.

    Please let me know if you need further explanation.

    Thanks,

    Amit Khanna

    Nicely done and good explanation... but, try it on a million row table and see how long it takes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 26, 2008 at 2:32 am

    #862962

    try this out

    select * ,op_bal + quantity from #Temps

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 1 through 15 (of 18 total)

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