use master 
go
create database bank_test;
go
use bank_test;
go

create table dbo.account
 (accno int primary key,
  begin_balance dec(18) not null default (0),
  last_processed_mutation int not null default (0), -- in begin_balance
  minimum_balance dec(18) not null default (0));
go

create table dbo.[transfer]
 (id int identity not null primary key,
  debaccno int not null references dbo.account (accno),
  credaccno int not null references dbo.account (accno),
  amount dec(18) not null,
  fromdate date not null default (getUTCdate()),
  processed bit not null default (0));
go
-- index to find the transfers to be processed
  -- especially if this is a small subset
create index idx_transfer_not_processed on dbo.[transfer] (id) 
  include (debaccno, credaccno, amount, fromdate, processed)
  where processed = 0
go       

create table dbo.account_mutation
 (id int identity not null primary key,
  [transfer] int not null references [transfer] (id),
  accno int not null references account (accno),
  amount dec(18) not null,
  date_time datetime2 not null default (getUTCdate()));
go
-- index to determine the balance
 -- this index fragments and range-scans are done; so it should be defragmented now and then
create index idx_account_mutation_accno on dbo.account_mutation (accno, id) include (amount);
go

-- testdata
-- 10K accounts
with n0 as (select 1 as c union all select 1)              --      2 rows
    ,n1 as (select 1 as c from n0 as a cross join n0 as b) --      4 rows
    ,n2 as (select 1 as c from n1 as a cross join n1 as b) --     16 rows
    ,n3 as (select 1 as c from n2 as a cross join n2 as b) --    256 rows
    ,n4 as (select 1 as c from n3 as a cross join n3 as b) --  65536 rows
    ,nums as (select row_number() over(order by (select null)) as n from n4)
insert dbo.account (accno, begin_balance, minimum_balance) 
  select n, n%10*100, -n%10*100
    from nums
    where n <= 10000;
-- 1M transfers to be processed
with n0 as (select 1 as c union all select 1)              --      2 rows
    ,n1 as (select 1 as c from n0 as a cross join n0 as b) --      4 rows
    ,n2 as (select 1 as c from n1 as a cross join n1 as b) --     16 rows
    ,n3 as (select 1 as c from n2 as a cross join n2 as b) --    256 rows
    ,n4 as (select 1 as c from n3 as a cross join n3 as b) --  65536 rows
    ,n5 as (select 1 as c from n4 as a cross join n2 as b) --1048576 rows
    ,nums as (select row_number() over(order by (select null)) as n from n5)
insert dbo.[transfer] (debaccno, credaccno, amount) 
  select (n%10000)+1, ((n+1)%10000)+1, n%100
    from nums
    where n <= 1000000;
go

-- inline table valued function to determine current and minimum balance
create or alter function dbo.tvf_acc_balance (@accno int) returns table
as
return (
  select isnull(sum(AM.amount), 0)+A.begin_balance balance, A.minimum_balance 
    from dbo.account A
      left join dbo.account_mutation AM with (updlock) 
        on AM.accno = A.accno 
       and AM.id > A.last_processed_mutation
    where A.accno = @accno
    group by A.accno, A.begin_balance, A.minimum_balance, A.last_processed_mutation
);
go

-- procedure for processing a single transfer
create or alter proc dbo.usp_transfer 
  @id int
 ,@debaccno int
 ,@credaccno int
 ,@amount dec(18)
as
begin try;
  begin tran;
    -- balancecheck
    if exists 
     (select * 
        from dbo.tvf_acc_balance(@debaccno) 
        where balance - @amount >= minimum_balance)
    begin; -- balance is ok
      -- bijboeken
        insert dbo.account_mutation (accno, amount, [transfer]) 
          values (@credaccno, @amount, @id);
      -- afboeken
        insert dbo.account_mutation (accno, amount, [transfer]) 
          values (@debaccno, -@amount, @id);
      -- statusupdate
        update dbo.[transfer]
          set processed = 1
          where id = @id;
    end;
    else 
    begin; -- insufficient balance, postpone processing with 1 day
      update dbo.[transfer]
        set fromdate = dateadd(dd, 1, getUTCdate())
        where id = @id;
    end;
  commit tran;
end try
begin catch
  rollback tran;
end catch;
go

-- process batch of transfers 
create or alter proc process_transfers_serially @number int 
as
set nocount on;
set transaction isolation level serializable;
declare @id int
       ,@debaccno int
       ,@credaccno int
       ,@amount dec(18);
declare c_transfer cursor forward_only for 
  select top (@number) id, debaccno, credaccno, amount
    from dbo.[transfer]
    where processed = 0
      and fromdate <= getUTCdate()
    order by id;
open c_transfer;
fetch next from c_transfer into @id, @debaccno, @credaccno, @amount;
while @@fetch_status = 0
begin;
  exec dbo.usp_transfer @id, @debaccno, @credaccno, @amount;
  fetch next from c_transfer into @id, @debaccno, @credaccno, @amount;
end;
close c_transfer;
deallocate c_transfer;
go

-- test batch of 100000
exec process_transfers_serially @number = 100000

-- results
select count(*) from dbo.account_mutation 
select top 100 * from dbo.account_mutation 
select top 100 A.accno, A.minimum_balance, A.begin_balance, S.balance 
  from account A 
    cross apply dbo.tvf_acc_balance(A.accno) S
select top 100 * -- transfers postponed
  from [transfer] 
  where fromdate > getUTCdate()