SQL Buckets Determine Age Grouping

Question

I have a database with legacy data that stores transactions and uses a "bucket" method for determining account balances. I need a way to get aged past due for accounts.

Table Transactions
TransactionId
TransactionType (CHARGE,RECEIPT)
Amount
PostDate

To get the current balance:

SELECT SUM(CASE TransactionTypeId WHEN RECEIPT THEN Amount * -1 ELSE Amount END) CurrentBalance

I need a way to determine past due 30, 60, 90, 120, etc:

Account Current 30 60 90 120+
12345 $50.00 $0.00 $25.00 $25.00 $0.00

I tried running separate queries and limiting the CHARGE postdates to greater than 30,60,90,120, running for each group and subtracting the others, etc but cannot get the expected results.

The table doesn't store a flag for past due, all balances are calculated on the fly.

Am I missing something simple? I tried a net search but not sure if there's a term for this type of sql query.

Database is SQL Server if that helps.

TIA

Answer 1

You could use an additional clause in the case to filter out transactions from the last 30 days. For example:

SELECT 
    SUM(
        CASE WHEN TransactionTypeId = 'RECEIPT' THEN -Amount 
             ELSE Amount 
    END) as CurrentDue
,   SUM(CASE WHEN datediff(d,PostDate,getdate()) <= 30 THEN 0
             WHEN TransactionTypeId = 'RECEIPT' THEN -Amount 
             ELSE Amount 
    END) as PastDue30
,   ...
FROM Transactions

To just exclude charges from the past 30 days, swap the when clauses:

,   SUM(CASE WHEN TransactionTypeId = 'RECEIPT' THEN -Amount 
             WHEN datediff(d,PostDate,getdate()) <= 30 THEN 0
             ELSE Amount 
    END) as PastDue30

Answer 2

This is what I ended up with, something that I had from before, but was missing the checks for when the amount due is zero, as well as checking if the prior group had a negative value. I had to add them because I was getting strange results, say if the account was overpaid for a service, which would have a negative due for the prior period.

SELECT
ServiceId,
AmountDue PastDue,
CASE AmountDue WHEN 0 THEN 0 ELSE CASE WHEN AmountDue60 < 0 THEN 0 ELSE AmountDue30 - AmountDue60 END END PastDue30,
CASE AmountDue WHEN 0 THEN 0 ELSE CASE WHEN AmountDue90 < 0 THEN 0 ELSE AmountDue60 - AmountDue90 END END PastDue60,
CASE AmountDue WHEN 0 THEN 0 ELSE CASE WHEN AmountDue120 < 0 THEN 0 ELSE AmountDue90 - AmountDue120 END END PastDue90,
CASE AmountDue WHEN 0 THEN 0 ELSE CASE WHEN AmountDue120 < 0 THEN 0 ELSE AmountDue120 END END PastDue120
FROM
(
    SELECT T.ServiceId,
    SUM(CASE WHEN T.TransactionTypeId = @Receipt THEN T.TAmount * -1 WHEN T.TransactionTypeId = @Charge THEN T.TAmount ELSE 0 END) AmountDue,
    SUM(CASE WHEN T.TransactionTypeId = @Receipt THEN T.TAmount * -1 WHEN T.TransactionTypeId = @Charge THEN CASE WHEN DATEDIFF(D, T.TPostDate, @TPostDate) >=  30 THEN T.TAmount ELSE 0 END ELSE 0 END) AmountDue30,
    SUM(CASE WHEN T.TransactionTypeId = @Receipt THEN T.TAmount * -1 WHEN T.TransactionTypeId = @Charge THEN CASE WHEN DATEDIFF(D, T.TPostDate, @TPostDate) >=  60 THEN T.TAmount ELSE 0 END ELSE 0 END) AmountDue60,
    SUM(CASE WHEN T.TransactionTypeId = @Receipt THEN T.TAmount * -1 WHEN T.TransactionTypeId = @Charge THEN CASE WHEN DATEDIFF(D, T.TPostDate, @TPostDate) >=  90 THEN T.TAmount ELSE 0 END ELSE 0 END) AmountDue90,
    SUM(CASE WHEN T.TransactionTypeId = @Receipt THEN T.TAmount * -1 WHEN T.TransactionTypeId = @Charge THEN CASE WHEN DATEDIFF(D, T.TPostDate, @TPostDate) >= 120 THEN T.TAmount ELSE 0 END ELSE 0 END) AmountDue120
    FROM Transactions T
    WHERE T.AccountId = @AccountId
    GROUP BY T.ServiceId
) AB