Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajnidas
Yak Posting Veteran
97 Posts |
 Posted - 2014-09-11 : 06:29:25
|
| hi all,SELECT debit.ACCOUNT_NO, debit.Serviced_Amt,credit.Tran_Amt,credit.Serviced_Flag FROM tbl_Interest_Debit as debit inner join tbl_Credit as credit on debit.ACCOUNT_NO=credit.Account_No order by credit.TRANSACTION_VALUE_DATE getting result-------------------ACCOUNT_NO Serviced_Amt Tran_Amt Serviced_Flag------------ --------------- ------------ ------------45505000661 1013088.00 19157467.00 045505000661 1013088.00 18988.96 033105126375 286533.00 171084.00 033105126375 286533.00 500000.00 033105126375 286533.00 1000000.00 033105126375 286533.00 800000.00 033105126375 286533.00 1000000.00 033105126375 286533.00 1000000.00 0I want that service_amount should be subtracted from tran_amt until service_amount become zero Once service_amount becomes zero service_flag should be changed to 1.thanks Rajnidas |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-09-11 : 07:50:28
|
| whenever the value is 0 after substarcting service_amount from trans_amount,you want to display the serviced_flag as 1?SELECT debit.ACCOUNT_NO, case when (debit.Serviced_Amt-credit.Tran_Amt)=0 then 1 else 0 end as serviced_flag FROM tbl_Interest_Debit as debit inner join tbl_Credit as credit on debit.ACCOUNT_NO=credit.Account_No order by credit.TRANSACTION_VALUE_DATEJaveed Ahmed |
 |
|
|
|
|
|
complex query