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

• sudhanva

  Ten Centuries

  Points: 1343

  More actions

  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

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

  123412246

  22468254

  32549263

  426411275

  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.

• Amit Khanna-455738

  SSC Eights!

  Points: 865

  More actions

  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

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  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

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

  123412246

  22468254

  32549263

  426411275

  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

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  August 22, 2008 at 1:32 am

  #861682

  nice solution amit.

  "Keep Trying"

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  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

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

  123412246

  22468254

  32549263

  426411275

  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.

• Amit Khanna-455738

  SSC Eights!

  Points: 865

  More actions

  August 22, 2008 at 2:00 am

  #861691

  Thanks chirag for the compliment. 🙂

  yes arjun you are right..

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  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

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

  123412246

  22468254

  32549263

  426411275

  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"

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  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

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

  123412246

  22468254

  32549263

  426411275

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

• sudhanva

  Ten Centuries

  Points: 1343

  More actions

  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.

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  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.

• sudhanva

  Ten Centuries

  Points: 1343

  More actions

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

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  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://www.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)
  

• Amit Khanna-455738

  SSC Eights!

  Points: 865

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  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)
  

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  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;-)