SQL: Creating aging buckets based on last payment date for financial reporting

Question

I need to create an aging report of credit balances on the customer-level.

Notes:

1. Aging is based on customer's last payment date.

2. 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.

The SQL to get the customers with the credit balances:

SELECT 
  ACCOUNT.CUST_ID
, sum(ACCOUNT.BALANCE)

FROM ACCOUNT

GROUP BY ACCOUNT.CUST_ID

HAVING sum(ACCOUNT.BALANCE) < 0

SQL to get latest payment date:

SELECT

  TRANSACTIONS.CUST_ID
, MAX(TRANSACTIONS.POST_DATE)

FROM TRANSACTIONS

WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'

GROUP BY TRANSACTIONS.CUST_ID

I need to create columns for aging buckets such as:

'0 - 30' CREDIT BALANCE SUM

'0 - 30' CREDIT BALANCE CUSTOMER COUNT

'31 - 60'...

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.

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?

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.

Answer 1

Your approach is the right approach. I'm not sure why you think doing something in a stored procedure would be more efficient than doing it in a query.

I think you want a query something like:

select (case when t.DaysAgo between 0 and 30 then '0-30'
             when t.DaysAgo between 31 and 60 then '31-60'
             else 'OLD'
        end),
       <whatever you want here>
from (SELECT ACCOUNT.CUST_ID, sum(ACCOUNT.BALANCE) as balance
      FROM ACCOUNT
      GROUP BY ACCOUNT.CUST_ID
     ) a left outer join
     (SELECT TRANSACTIONS.CUST_ID, MAX(TRANSACTIONS.POST_DATE) as LastPaymentDate,
             datediff(d, X(TRANSACTIONS.POST_DATE), getdate()) as DaysAgo, 
      FROM TRANSACTIONS
      WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'
      GROUP BY TRANSACTIONS.CUST_ID
     ) t
     on a.cust_id = t.cust_id 
group by  (case when t.DaysAgo between 0 and 30 then '0-30'
                when t.DaysAgo between 31 and 60 then '31-60'
                else 'OLD'
           end)

In general, you should let the SQL engine optimize the query. It is usually (but definitely not always!) going to do a good job, letting you think about what you want to accomplish.

You would have additional logic to exclude certain customers and bring in other fields.