SQL Buckets Defines an Age Group

Question

I have a database with outdated data that stores transactions and uses the "bucket" method to determine account balances. I need a way to get past due for bills.

Operations with tables
TransactionID Transaction
type (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 30, 60, 90, 120, etc .:

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

I tried to execute individual queries and limited the CHARGE postdates to more than 30,60,90,120, performing for each group and subtracting the rest, etc., but could not get the expected results.

The table does not store the delay flag, all balances are calculated on the fly.

Am I missing something simple? I tried network search, but not sure if there is a term for this type of sql query.

A database is SQL Server if that helps.

TIA

Answer 1

You can use the optional offer in caseto filter transactions for 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 simply exclude payments for the last 30 days, swap offers when:

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

Answer 2

, , - , , , , , . , , , , .

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