Click here to Skip to main content
15,849,130 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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

1 solution

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900