title: Apply payments to charges recursively SQL Server
body: <p>I have in a single table both charges and payments. Payments are "applied" to charges in order to balance the account to zero. There is no specific order to apply, as long as all charges are balanced to zero (or most of them).</p>
  
  <p>After the SP I'm trying to build there would be two possible states of the table. As I said, I don't care how they are "applied" as long as most of charges' balances is zero.</p>
  
  <pre><code>CREATE TABLE dbo.Transactions (
  TrxID       INT IDENTITY,
  TrxType     BIT, -- 1 for Charges, 0 for Payments
  TrxDescription  VARCHAR(MAX),
  Amount  DECIMAL(13,2),
  ApplyTo     INT -- TrxID of the charge to which the payment is "applied"
  );
  
  INSERT INTO dbo.Transactions VALUES(1,'Charge1',100,NULL);
  INSERT INTO dbo.Transactions VALUES(0,'Payment1',-80,NULL);
  INSERT INTO dbo.Transactions VALUES(0,'Payment2',-15,NULL);
  INSERT INTO dbo.Transactions VALUES(1,'Charge3',200,NULL);
  INSERT INTO dbo.Transactions VALUES(0,'Payment4',-20,NULL);
  INSERT INTO dbo.Transactions VALUES(0,'Payment5',-80,NULL);
  INSERT INTO dbo.Transactions VALUES(0,'Payment6',-105,NULL);
  
  SELECT * FROM dbo.transactions
  
  SELECT SUM(Amount) FROM dbo.Transactions;
  SELECT SUM(Amount) FROM dbo.Transactions WHERE TrxType=1;
  SELECT SUM(Amount) FROM dbo.Transactions WHERE TrxType=0;
  
  -- CORRECT APPLICATION
  UPDATE dbo.Transactions SET ApplyTo=1 WHERE TrxID IN(2,5)
  UPDATE dbo.Transactions SET ApplyTo=4 WHERE TrxID IN(3,6,7)
  
  -- The global balance is zero
  SELECT SUM(Amount) FROM dbo.Transactions
  
  -- Both charges have zero balance
  SELECT t.*,t.Amount+b.Balance 'Balance'
  FROM dbo.Transactions t
  OUTER APPLY (SELECT SUM(t2.Amount)Balance
           FROM dbo.Transactions t2
           WHERE t.TrxID=t2.ApplyTo
  )b
  
  -- WRONG APPLICATION
  UPDATE dbo.Transactions SET ApplyTo=1 WHERE TrxID IN(2,3,5)
  UPDATE dbo.Transactions SET ApplyTo=4 WHERE TrxID IN(6,7)
  
  -- The global balance is zero
  SELECT SUM(Amount) FROM dbo.Transactions
  
  -- Charges dont have correct balance, as they could be both zero if applied correctly
  SELECT t.*,t.Amount+b.Balance 'Balance'
  FROM dbo.Transactions t
  OUTER APPLY (SELECT SUM(t2.Amount)Balance
           FROM dbo.Transactions t2
           WHERE t.TrxID=t2.ApplyTo
  )b
  </code></pre>
  
  <p>Thanks.</p>