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.

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

Solution 2

I need clarification on one thing. If a customer may have more than one account, and if a payment may be posted on the incorrect account, how do you handle that?

Meaning, if a customer has more than one account how do you determine a payment is posted into the wrong account. Does it mean even if a certain customer has more than one account, the customer still has the same CustID for different accounts?

Because if the CustID is different for each account, I truly believe there's no way to handle that properly rather than using the old fashion way in Excel you're currently using.

Thanks

Comments

[texas_mike81]

Customer has same customer ID for multiple accounts. When payments are applied to the wrong account, they are almost always applied to one of the customer's other accounts. So taking the sum of all accounts by customers ensures that only customers with true negative balances are selected.

[VAAA]

@Gordon I publish this question stackoverflow.com/questions/11592880/sql-aging-report that is similiar of this answer but my problem is that I want to do that but only with 1 table. Hope you can help me. Thanks