title: Calculate loan payments & when the payment was satisfed - partial payments allowed
body: <p>I've been writing SQL queries for years but I'm stuck on this one.</p>
  
  <p>I've got 2 tables in MySQL:</p>
  
  <ul>
  <li>LOANPAYMENTSDUE includes <code>LoanPaymentsDueId</code>, <code>LoanId</code>, <code>AmtDue</code>, <code>DueDate</code></li>
  <li>LOANPAYMENTS includes <code>LoanPaymentsId</code>, <code>LoanId</code>, <code>AmtPaid</code>, <code>PaidDate</code></li>
  </ul>
  
  <p>The relationship between the tables is the <code>LoanId</code> and not the specific payment that is due. In a perfect world the <code>DueDate = PaidDate</code> and the <code>AmtDue = AmtPaid</code>.  However, what is making this complex for me is no relationship between the <code>LoanPaymentsDueId</code> and the <code>LoanPaymentsId</code>. The relationship only exists at the <code>LoanId</code> allowing for partial payments to be made on a single LOANPAYMENTSDUE payment.</p>
  
  <p>I've researched the web trying to find the right query to <strong>create a report showing the date that each LOANPAYMENTSDUE was satisfied.</strong>  This requires calculating the balance as of the LOANPAYMENTSDUE.DueDate because there can be payments missed and a new payment should satisfy the balance of the oldest LOANPAYMENTSDUE payment.</p>
  
  <p>Here is the sample data and table scripts:</p>
  
  <pre><code>CREATE TABLE LOANPAYMENTSDUE (
  LoanPaymentsDueId BIGINT(20) NOT NULL AUTO_INCREMENT
  , LoanId BIGINT(20)
  , AmtDue double NOT NULL
  , DueDate date NOT NULL
  , PRIMARY KEY (LoanPaymentsDueId)
  );
  
  INSERT INTO LOANPAYMENTSDUE (LoanId, AmtDue, DueDate) VALUES (1, 100, '2013-07-15');
  INSERT INTO LOANPAYMENTSDUE (LoanId, AmtDue, DueDate) VALUES (1, 100, '2013-08-15');
  INSERT INTO LOANPAYMENTSDUE (LoanId, AmtDue, DueDate) VALUES (1, 100, '2013-09-15');
  INSERT INTO LOANPAYMENTSDUE (LoanId, AmtDue, DueDate) VALUES (1, 100, '2013-10-15');
  INSERT INTO LOANPAYMENTSDUE (LoanId, AmtDue, DueDate) VALUES (1, 100, '2013-11-15');
  
  CREATE TABLE LOANPAYMENTS (
  LoanPaymentsId BIGINT(20) NOT NULL AUTO_INCREMENT
  , LoanId BIGINT(20)
  , AmtPaid double NOT NULL
  , PaidDate date NOT NULL
  , PRIMARY KEY (LoanPaymentsId)
  );
  
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 100, '2013-07-15'); /* Full pmt on due date */
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 100, '2013-08-10'); /* Full pmt a few days early */
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 100, '2013-09-22'); /* Full pmt a week late */
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 50, '2013-10-18'); /* Partial pmt a few days late */
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 50, '2013-11-07');/* Partial pmt 3 weeks late and satisfies the 10/15/2013 balance on this date */
  INSERT INTO LOANPAYMENTS (LoanId, AmtPaid, PaidDate) VALUES (1, 100, '2013-11-22');/* Full pmt a week late and satisfies the 11/15/2013 pmt due */
  </code></pre>
  
  <p>The report query should simply provide the PAIDDATE when each LOANPAYMENTSDUE was satisfied. Using the table data above the report would be as follows:</p>
  
  <pre><code>LOANID     LOANPAYMENTSDUEID     AMTDUE     DUEDATE          PAIDDATE
  1          1                     100        2013-07-15       2013-07-15    
  1          2                     100        2013-08-15       2013-08-10
  1          3                     100        2013-09-15       2013-09-22
  1          4                     100        2013-10-15       2013-11-07
  1          5                     100        2013-11-15       2013-11-22  
  </code></pre>