How to get first row value into the next row

• jayakrishna.alla

  Grasshopper

  Points: 12

  More actions

  March 12, 2012 at 12:31 pm

  #252430

  Hai Sir if u feel free plz reply me

  I have a table A

  in the table data contain like below

  ID TransactionDate Withdraw Deposit

  1 01/03/2012 25000 Null

  1 03/03/2012 Null 5000

  1 10/03/2012 Null 5000

  I need data like below

  ID TransactionDate TransactionDate Withdraw Deposit Balance

  1 01/03/2012 03/03/2012 25000 5000 20000

  1 03/03/2012 10/03/2012 20000 5000 15000

  Can u possible plz answer me

  i am using sql2005

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  March 12, 2012 at 12:55 pm

  #1458102

  There are a number of challenges you are going to face here. First it seems your Withdraw and Deposit are backwards?

  Why have two columns? Aren't these all transactions that have a type of either Withdraw or Deposit?

  I don't get the ID column since they are all the same. Maybe that is the AccountID and your sample data just has one?

  Since you are new around here I created the type of information for you that you should post in the future. This is ddl and sample data.

  create table #TransData

  (

  TransactionDate datetime,

  Withdraw numeric(9,2),

  Deposit numeric(9,2)

  )

  insert #TransData (TransactionDate, Deposit, Withdraw)

  values ('1/3/2012', 25000, null),

  ('3/3/2012', null, 5000),

  ('10/3/2012', null, 5000)

  select * from #TransData

  drop table #TransData

  So now we all have the same information as base for our discussion.

  All of this type of stuff really belongs in the front end and not in sql but if you are determined to do this in the wrong place, keep reading.

  I don't really understand your requirements here. Part of what you are looking for is a running total. Jeff Moden has a great article about doing that here. http://www.sqlservercentral.com/articles/T-SQL/68467/%5B/url%5D. This should answer how you go about the total column

  The next piece of this puzzle is you have parts of more than 1 row on each row in your output. It is pretty much impossible to tell what is going on in your desired output. What happens when you have a Deposit or Withdrawal on two subsequent rows? Which one gets moved over to the Deposit column? How in the world can you explain a running total that now looks like it is incorrect?

  If you need some help with this you will have to help us understand more clearly what you are trying to do.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 12, 2012 at 12:58 pm

  #1458108

  Please read the first article I reference in my signature block below regarding asking for help. Follow the instructions on what and how to post the information we need to help you. Be sure to post the expected results based on your sample data.