如何将第一行的值收集到第二行 [英] How to collect first row values into the second row

问题描述

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

推荐答案

根据您的要求，这不是一个完美的解决方案，但与您的答案有些接近.

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

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

注意:T1是您的表名称

稍加使用上面的查询，您可能会得到完美的解决方案.

NOTE : T1 is your Table Name

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

这篇关于如何将第一行的值收集到第二行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！