title: SQL: Creating aging buckets based on last payment date for financial reporting
body: <p>I need to create an aging report of credit balances on the customer-level.  </p>
  
  <p>Notes: </p>
  
  <ol>
  <li><p>Aging is based on customer's last payment date.</p></li>
  <li><p>A customer can have multiple accounts, and there are sometimes errors in which a payment is applied to the wrong account.  For example, a customer with a $15 balance on an account makes a $15 payment.  That $15 payment may be applied to the wrong account, leaving the customer with a $-15 balance on one account and a $15 balance on another.  This customer needs to be excluded from the report. </p></li>
  </ol>
  
  <p>The SQL to get the customers with the credit balances:</p>
  
  <pre><code>SELECT 
    ACCOUNT.CUST_ID
  , sum(ACCOUNT.BALANCE)
  
  FROM ACCOUNT
  
  GROUP BY ACCOUNT.CUST_ID
  
  HAVING sum(ACCOUNT.BALANCE) &lt; 0
  </code></pre>
  
  <p>SQL to get latest payment date:</p>
  
  <pre><code>SELECT
  
    TRANSACTIONS.CUST_ID
  , MAX(TRANSACTIONS.POST_DATE)
  
  FROM TRANSACTIONS
  
  WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'
  
  GROUP BY TRANSACTIONS.CUST_ID
  </code></pre>
  
  <p>I need to create columns for aging buckets such as:</p>
  
  <p>'0 - 30' CREDIT BALANCE SUM</p>
  
  <p>'0 - 30' CREDIT BALANCE CUSTOMER COUNT</p>
  
  <p>'31 - 60'...</p>
  
  <p>I was going to use CASE statements using the DATEDIFF function between the max(TRANSACTIONS.POST_DATE) and "yesterday" - DATEADD(dd,-1,getdate()) to create the buckets.  </p>
  
  <p>However, wouldn't it be much more efficient to do this using variables or a stored procedure to get separate the customers based on last payment date before performing the bucket sum and count calculations?</p>
  
  <p>Any ideas regarding how to do this accurately and efficiently?  So far, I've been picking up the customers with credit balances along with their last payment date in a simple query and then creating the aging buckets myself using Excel.   </p>