SQL 中的银行交易

梅丽娜·夏尔马

我有一个名为 BankTransaction 的表：

Create table BankTransaction 
(
TransactionID int IDENTITY(1,1),
AccountNumber varchar(25) Not Null,
TransactionDate datetime not null Default getdate(), 
TransactionType varchar(25) Not Null,
TransactionAmount money Default '0',
BalanceAsOf money Default '0' ,
Primary Key(TransactionID) 
);


Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Credit','22535.215');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Debit','215.9');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Credit','500');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Debit','100');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Credit','5000');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Debit','100');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Debit','235.215');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Credit','1000');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Credit','500');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('7865HNH','Credit','982000');

我想要的是，如果 TransactionType 是 Debit，则帐户具有相同的 AccountNumber，然后从 BalanceAsOf 中减去，如果 TransactionType 是 Credit，则添加到 BalanceAsOF：

这是我尝试过但不起作用的方法：

update bt
                        Set  BalanceAsOF = case 

                        when AccountNumber=AccountNumber and  TransactionType = 'Credit' 
                        then ( BalanceAsOf + TransactionAmount )
                        when  AccountNumber=AccountNumber AND TransactionType='Debit' 
                        then BalanceAsOf - TransactionAmount
                        End 
                        from
                        dbo.BankTransaction as bt

                        Select * From dbo.BankTransaction as p

尼拉詹·博卡雷尔

这正是你应该做的

set nocount on;

Create table BankTransaction 
(
TransactionID int IDENTITY(1,1),
AccountNumber varchar(25) Not Null,
TransactionDate datetime not null Default getdate(), 
TransactionType varchar(25) Not Null,
TransactionAmount money Default '0',
BalanceAsOf money Default '0' ,
Primary Key(TransactionID) 
);


Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Credit','22535.215');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Debit','215.9');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Credit','500');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Debit','100');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Credit','5000');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Debit','100');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('123AABDF','Debit','235.215');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2256DF','Credit','1000');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('2351AV','Credit','500');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('7865HNH','Credit','982000');

Insert into dbo.BankTransaction(AccountNumber,TransactionType,TransactionAmount) 
Values ('876YYT','Credit','27363647');


  Create Table #AccountNomber (
  AccountNumber varchar(25) Not Null,);

insert into #AccountNomber ( AccountNumber)
Select  Distinct BT.AccountNumber 
  From dbo.BankTransaction as BT 

  --select * from #AccountNomber


   Declare @PreviousBalance money
   Set @PreviousBalance = 0 

   declare @AcctNumber varchar(25)
   declare @TransactionId int

     --update b 
     --set b.BalanceAsOf=0
     --from BankTransaction b

   while exists(select 1 from #AccountNomber)
   begin
     select @AcctNumber = a.AccountNumber from #AccountNomber a

     --select 'test',* from BankTransaction b
     --where b.AccountNumber=@AcctNumber
     --and b.BalanceAsOf=0
     --order by b.TransactionID
     --/*
     while exists (select 1 from BankTransaction b where b.AccountNumber=@AcctNumber
     and b.BalanceAsOf=0
     )
     begin
     select @TransactionId = (select top 1 b.TransactionID from BankTransaction b
     where b.AccountNumber=@AcctNumber
     and b.BalanceAsOf=0
     order by b.TransactionID)

     select @PreviousBalance =@PreviousBalance + case when b.TransactionType='Credit' then b.TransactionAmount else -1*b.TransactionAmount end
     from BankTransaction b
     Where b.AccountNumber=@AcctNumber
     and b.TransactionID=@TransactionId

     update b 
     set b.BalanceAsOf=@PreviousBalance
     from BankTransaction b
     Where b.AccountNumber=@AcctNumber
     and b.TransactionID=@TransactionId

     end
    select @PreviousBalance =0
    delete a from #AccountNomber a
    where a.AccountNumber=@AcctNumber

   End





   select * from BankTransaction b
   order by b.TransactionID

   Drop table BankTransaction 
   drop table #AccountNomber

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。