SQL Buckets Determine Age Grouping

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 expec开发者_如何学Goted 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

---

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

---

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