Aggregate Balance For Account Between Rows Based On Date and ID

I’m trying to write a query that will aggregate the balance of open user accounts, but I’m having trouble getting them to add up in the desired order. The code should sum up the balance for every different row under an account in the order of oldest to newest and, if there are more than one row for a particular date, in order of ID from highest to lowest. The issue is mainly a problem on accounts with multiple openings on the same day because the entire balance for the day gets lumped together, instead of being calculated individually.

Here’s an example of what I want.

Right now, I am pulling the data by joining a second copy of the table and running a SUM of Balance and AvailableCredit where the AccountNumber is the same and the OpenDate is <= the first table. This means that anything on the same day gets SUMmed together, like this:

This all seems clunky to begin with, and I can’t seem to get the code to do what I want. Any help would be appreciated, thank you. A version of my current code is included below.

SELECT AccountNumber, 
       ID, 
       OpenDate, 
       Balance, 
       AvailableCredit, 
       (Balance + AvailableCredit) AS TotalBalance,
       (SELECT SUM(Balance) + SUM(AvailableCredit)
        FROM Table AS T2
        WHERE T2.AccountNumber = AccountNumber
          AND T2.OpenDate <= OpenDate) AS Aggregate
FROM Table

I have tried using GROUP BY clauses and PARTITION/OVER clauses to get the data to aggregate properly, but they never behave the way I expect them to. I also tried adding a T2.ID >= ID clause to the joined table, but that didn’t work how I thought it would either.

Thanks again!

You need to sign in to view this answers