How to collect first row values into the second row

Question

1.00/5 (2 votes)

See more:

SQL-Server-2005

Auctal Table data like below						
GLNO	LoanNo	TRANSACDATE	WITHDRAWAMOUNT	COLLECTIONAMOUNT		
1170	553	01/04/2010	25000	             NULL		
1170	553	10/04/2010	NULL	             5000		
1170	553	15/04/2010	NULL	             5000		
1170	553	20/04/2010	NULL	             10000

in the above table data if WithdrawAmount is not null then  TRANSACDATE is WithdrawDate	and same as COLLECTIONAMOUNT
Above Table data I want to llike this						
Glno	LoanNo	WithdrawDate	WithdrawAmount	Colldat	   CollAmount   bal	
1170	553	01/04/2010 0:00	25000	       10/04/2010   5000       20000.00
1170	553	10/04/2010 0:00	20000	       15/04/2010   5000       15000.00
1170	553	15/04/2010 0:00	15000	       20/04/2010   10000	5000.00

Posted 14-Mar-12 7:45am

JKChowdary

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

P.S Vijay · Answer 1 · 2012-03-14T20:28:00

It is not a perfect solution as per your requirement but it is some thing near with your answer.

SQL

Select (Select Count(*) from t1 as Tab1 where Tab1.TransAcDate <= t1.TransAcDate)  as SrNo
,GLNo , LoanNo , TransAcDate
,(Select Sum(Isnull(WITHDRAWAMOUNT,0))-SUM(Isnull(COLLECTIONAMOUNT,0))
from t1 as Tab1 where Tab1.TransAcDate <= t1.TransAcDate) as WithdrawAmount
, TransAcDate as COLLECTIONDate
,  COLLECTIONAMOUNT
,ABS((Select Sum(Isnull(WITHDRAWAMOUNT,0))-SUM(Isnull(COLLECTIONAMOUNT,0))
from t1 as Tab1 where Tab1.TransAcDate <= t1.TransAcDate)  - Isnull(COLLECTIONAMOUNT,0 )) as bal
from t1
Order by SrNo

NOTE : T1 is your Table Name

Little bit play with the above query you may be get the perfect solution.