How to efficiently get an account balance for every day from Transactions table?

[Thomasso]

Hello,

I have these tables (SQL Server 2019 Express):

CREATE TABLE tbl1Accounts (
    AccountID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    AccountTypeID INT NOT NULL FOREIGN KEY REFERENCES tbl2AccountTypes (AccountTypeID),
    CurrencyID INT NOT NULL FOREIGN KEY REFERENCES tbl1Currencies (CurrencyID),
    AccountCode NVARCHAR(10) NOT NULL UNIQUE,
    AccountName NVARCHAR(50) NOT NULL UNIQUE,
    StartingBalance DECIMAL(10,2) NOT NULL,
    IsHidden BIT NOT NULL DEFAULT 0,
    DateAccountClosed DATE NULL,
    CONSTRAINT CH_tbl1Accounts_DateAccountClosed CHECK (DateAccountClosed <= GETDATE()),
    CONSTRAINT CH_tbl1Accounts_IsHidden_DateAccountClosed CHECK ((DateAccountClosed IS NOT NULL AND IsHidden = 1) OR (DateAccountClosed IS NULL))
)

CREATE TABLE tbl1Transactions (
    TransactionID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TransactionTypeID INT NOT NULL FOREIGN KEY REFERENCES tbl2TransactionTypes (TransactionTypeID),
    PartnerID INT NULL FOREIGN KEY REFERENCES tbl1Partners (PartnerID),
    AccountFromID INT NULL FOREIGN KEY REFERENCES tbl1Accounts (AccountID),
    AccountToID INT NULL FOREIGN KEY REFERENCES tbl1Accounts (AccountID),
    TransactionDate DATE NOT NULL,
    AmountFrom DECIMAL(10,2) NULL,
    AmountTo DECIMAL(10,2) NULL,
    CZKRate DECIMAL(8,4) NULL,
    TransactionDescription NVARCHAR(255) NULL,
    Notes NVARCHAR(MAX) NULL,
    CONSTRAINT CH_tbl1Transactions_TransactionTypeID CHECK ((TransactionTypeID = 1 AND AccountFromID IS NULL AND AccountToID IS NOT NULL AND AmountFrom IS NULL AND AmountTo IS NOT NULL) OR
                                                            (TransactionTypeID = 2 AND AccountFromID IS NOT NULL AND AccountToID IS NULL AND AmountFrom IS NOT NULL AND AmountTo IS NULL) OR
                                                            (TransactionTypeID = 3 AND AccountFromID IS NOT NULL AND AccountToID IS NOT NULL AND AmountFrom IS NOT NULL AND AmountTo IS NOT NULL)),
    CONSTRAINT CH_tbl1Transactions_TransactionDate CHECK (TransactionDate <= GETDATE()),
    CONSTRAINT CH_tbl1Transactions_CZKRate CHECK (CZKRate > 0)
)

I would like to get daily balances for each account (and display it as a line chart) based on SUMS of transactions associated with this account.

I could be summing all transactions until that respective day, but I fear that it wouldn't be very efficient, especially with many transactions.

Is there a correct way to do this? I assume that this is quite a basic request in data analytics.

Thank you in advance.
Tomas

[Welshgasman]

That is the way it is done, at least to my understanding. You do need some sort of index to compare.

Plus been asked multiple times, everywhere. Try Google as well.
Make sure relevant fields are indexed.

[madpiet]

Like a running total? Won't break over years unless you tell it to. (May require a date table for that).

What column do you want a running total for? Amount_from? Amount_to? Look up windowing functions.

the basic pattern (if you want to go "from the beginning") is something like

SELECT TransactionDate, AmountFrom, 
rtAmtFrom = SUM(AmountFrom) OVER (PARTITION BY <AccountNumber> ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
AmountTo,
rtAmtTo = SUM(AmountTo) OVER (PARTITION BY <AccountNumber> ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
FROM tblTransactions;

The grouping inside the window is done by the PARTITION BY (it's the grouping function for a window)

Oh wait. this is a running total. If you want a total for each day, then you just group by the TransactionDate and sum the amount. Not sure about the AmountFrom, AmountTo stuff. You could maybe speed it up by indexing on the date.

SELECT TransactionDate, SUM(AmountFrom) AS TotalFrom
FROM MyTable
GROUP TransactionDate
ORDER BY TransactionDate;

Helps a LOT LOT LOT if you at least INCLUDE the TransactionDate in the index on the table. (Check out the syntax etc for INCLUDE on the msft website.

[CJ_London]

I would include your starting balance as a transaction in the transaction table rather than as a field in the accounts table

you also need to clarify what you mean by daily balances - every day regardless of whether there is a transaction? Or just those dates where there is a transaction? I suspect the former given this is for a line chart, in which case we also need to know how you are specifying a date range

[Thomasso]

Hello,

Apologies, I think I wasn't perfectly clear.

To clarify my data model:

• AccountFrom/AccountTo are there because I also do transfers between accounts (therefore if a transaction is an expenditure, it will be only as AccountFrom/AmountFrom, if it's an income, then only as AccountTo/AmountTo, and if it's a transfer, both will be used. An SQL View deals with correct data display (positive/negative values), and a transfer will be actually shown as 2 transactions in the View (using a left join and UNION statement)
• As I am not an English native speaker, I am not familiar with a term "running balance". I want to display Balances for each account, by the END OF EVERY DAY, even if there is no transaction that day

For example:

Account Starting Balance = 1000

2025-01-01 Transaction 1 = +100
2025-01-01 Transaction 2 = -50

Account Balance 2025-01-01 = 1050

2025-01-02 No Transactions

Account Balance 2025-01-02 = 1050

2025-01-03 Transaction3 = +500

Account Balance 2025-01-03 = 1550

• I am not familiar with technique OVER (PARTITION BY), and also ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, I guess I will need to do some reading about those
• Why does it help to INCLUDE the TransactionDate in the index? And what does it actually mean?
• Why would you include the starting balance as a transaction instead of a table field?
• I am not sure what you mean by "how you are specifying a date range" - can you please clarify?

Thank you.
Tomas

[madpiet]

Hello,

Apologies, I think I wasn't perfectly clear.

To clarify my data model:

• AccountFrom/AccountTo are there because I also do transfers between accounts (therefore if a transaction is an expenditure, it will be only as AccountFrom/AmountFrom, if it's an income, then only as AccountTo/AmountTo, and if it's a transfer, both will be used. An SQL View deals with correct data display (positive/negative values), and a transfer will be actually shown as 2 transactions in the View (using a left join and UNION statement)
• As I am not an English native speaker, I am not familiar with a term "running balance". I want to display Balances for each account, by the END OF EVERY DAY, even if there is no transaction that day

For example:

• I am not familiar with technique OVER (PARTITION BY), and also ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, I guess I will need to do some reading about those
• Why does it help to INCLUDE the TransactionDate in the index? And what does it actually mean?
• Why would you include the starting balance as a transaction instead of a table field?
• I am not sure what you mean by "how you are specifying a date range" - can you please clarify?

Thank you.
Tomas

Okay, let me start at the beginning! (A very good place to start... )
If you want to show the balance at the END OF EVERY DAY <g>, then you need a way to force the existence of each date in your range, which you can do by creating a Calendar table, which contains a record for each date in your date range. Something simple like

CREATE TABLE Calendar (TheDate DATE PRIMARY KEY);

will do that.

Then you need to LEFT JOIN that to your Transactions table:

SELECT c.TheDate, SUM(t.[SomeAmount])
FROM Calendar c
LEFT JOIN Transactions t ON c.TheDate = t.TransactionDate
WHERE ...
GROUP BY c.TheDate
ORDER BY c.TheDate;

If you need to sum both the positive and the negative, you could just do SUM(t.[PositiveAmount]) + SUM(t.[NegativeAmount]) in your SELECT.

You wouldn't need a windowing function (which is what PARTITION is part of) to do this.

[CJ_London]

I want to display Balances for each account, by the END OF EVERY DAY, even if there is no transaction that day

in that case you will need another table to list every day and left join it to your transactions However I seem to recall SQL server has a function that does that, but I could be wrong.

• Why does it help to INCLUDE the TransactionDate in the index? And what does it actually mean?

indexing a field massively improves performance - particularly with what you are trying to do

• Why would you include the starting balance as a transaction instead of a table field?

makes for easier calculation

I am not sure what you mean by "how you are specifying a date range" - can you please clarify?

lets say your accounts start from 1st Jan 2020. is your date range always from 1st Jan 2020 to today? from 10th Feb 2023 to 25th July 2024? for today v yesterday? You said this was for a line chart so I suspect you need the second option (change dates to suit)

I don't have a requirement for every day, but I would expect to have something like

SELECT cDate, AccountID, sum(amount) AS Balance
FROM tblDates LEFT JOIN tblTrans ON tblTrans.tranDate<=tblDates.cDate
WHERE cDate between [startDate] and [End Date] AND tblTrans.AccountID=1234
GROUP BY cDate
ORDER BY cDate

tables re the above would be

tblDates
cDate - indexed, no dups

tblTrans
AccountID - indexed FK to the account table
tranDate - indexed, date of transaction
Amount - currency or decimal, can be positive or negative

[madpiet]

If you have a Calendar table, you could add a column that stores the calculated value for the daily starting balance. Then you'd only have to calculate it for new stuff. Yeah yeah yeah, I know all about "not storing calculated values" nonsense, but if it's never going to change after that date, then what's the difference? Then you can include the daily balance in the index if you want by using INCLUDE. Super fun, right?

[CJ_London]

but if it's never going to change after that date

it depends on the context which the OP hasn't really clarified. If these were bank accounts, I'm inclined to agree - but if there could be multiple transactions in a day, you also need a time element or don't update the field until the following day.

If on the other hand this is more an accounting system with suppliers and customers there is more to consider

[madpiet]

Well, unless you calculate up to the previous day. One good argument for splitting the date and the time into separate columns.

[ArviLaanemets]

...Yeah yeah yeah, I know all about "not storing calculated values" nonsense, but if it's never going to change after that date, then what's the difference?...

In real life, when working with data from some ERP systems, I have seen enough cases, where goods arriving to store are registered several days later for various reasons (there are separate date fields for registering date and arrival date). And you can't edit any registered entries afterwards, but sometimes correction entries for same order or invoice or whatever are added. E.g. at start the entry was registered from delivery documents, but afterwards was discovered the quantity didn't match. Or some of arrived goods were refuse, and were sent back to supplier.

Unless the order of entering info has very strict rules, the only ways to keep daily balances okey are:
run a procedure at every night to recalculate all daily balances (i.e. you'll have those balanses until yesterday, but nothing for today);
run a procedure to calculate ALL balances whenever a new transaction is registered.

But a real point is, storing such info is pointless. You calculate it whenever there is a need, and display it by report.

[NoellaG]

to speed up the solution:
* create an indexed view on the total transactions/day https://learn.microsoft.com/en-us/sq...l-server-ver16 .
* create a balance table where you add a line each month on the month closing date with the balance amounts at the end of the month = starting balance next month. Question of not having to calculate results over 2 years of data.
* as you are using SQL express you won't be able to run this monthly operation through a job, but you can use Powershell to schedule this operation
* with those tools you can use windowing functions https://www.brentozar.com/sql-syntax...es-sql-server/ and select case instructions to create a view with the daily balances

Tip: to get deeper into SQL Server : check out the posts of Brent Ozar

[jojowhite]

i made an access database to demonstrate what i think is your goal.

tbl1Accounts and tbl1Transactions are are local tables in this demo, but you can create a Linked
table from your SQL Server tables.

i also created 3 extra tables the purpose of which is to create a series of date (see query qryDates).
i also create another query, qryFirstLastTrans to get the first and the last transaction dates from you transactions.
see the last 2 queries, qryAccountDateTrans and qryRunningBalance.

as much as i wanted using qryRunningBalance to the report, but i am encountering some errors (and don't know
what is the issue). so i just use qryAccountDateTrans as the recordsource of the report rptRunningTotal.

please see all table structures and query designs so you can get to know what is going on.

[Thomasso]

Thanks guys, I already managed to create a calendar table and started working with OVER.

I can figure out displaying totals for each day, but I am struggling with grouping the results by accounts.

I have this calendar table:


This accounts table:


And this view for transactions:


I would like to create a running balances view for each account, with these desired results:

CalendarDate	AccountID	RunningBalance	Note
2025-01-01	14	376,94	No transactions yet => starting balance
2025-01-01	15	-18629,34	No transactions yet => starting balance
2025-01-01	22	200,00	No transactions yet => starting balance
2025-01-02	14	376,94	No transactions yet => starting balance
2025-01-02	15	-19848,34	3 transactions (+ starting balance)
2025-01-02	22	200,00	No transactions yet => starting balance
2025-01-03	14	376,94	No transactions yet => starting balance
2025-01-03	15	-19848,34	No transactions today
2025-01-03	22	200,00	No transactions yet => starting balance
2025-01-04	14	376,94	No transactions yet => starting balance
2025-01-04	15	-20548,34	1 transaction
2025-01-04	22	200,00	No transactions yet => starting balance
2025-01-05	14	376,94	No transactions yet => starting balance
2025-01-05	15	-20548,34	No transactions today
2025-01-05	22	200,00	No transactions yet => starting balance
2025-01-06	14	376,94	No transactions yet => starting balance
2025-01-06	15	-20548,34	No transactions today
2025-01-06	22	170,00	1 transaction (-30)
2025-01-07	14	376,94	No transactions yet => starting balance
2025-01-07	15	-36775,75	2 transactions
2025-01-07	22	90,00	1 transaction (-80)
2025-01-08	14	25078,19	2 transactions (+61477, -36775,75), plus starting balance.
2025-01-08	15	-36775,75	No transactions today
2025-01-08	22	90,00	No transactions today

I tried this query, but the results are not what I am looking for:

SELECT CAL.Date, TRA.AccountID, ACC.StartingBalance + SUM(TRA.Amount) OVER (PARTITION BY TRA.AccountID ORDER BY CAL.Date) AS RunningBalance

FROM tbl2Calendar CAL    LEFT JOIN v_Transactions TRA ON CAL.Date = TRA.TransactionDate
                        JOIN tbl1Accounts ACC ON TRA.AccountID = ACC.AccountID

WHERE CAL.Date BETWEEN '2025-01-01' AND '2025-01-08'
      AND TRA.AccountID IN (14,15,22)

ORDER BY CAL.Date, TRA.AccountID

Results:


The problems:

• this completely ignores the days before the first transaction (where I want to display the starting balance)
• it doesn't show balances for each day, but only for days when a transaction happened on that account
• it displays duplicate rows, such as row 1 to 4 or row 6 to 7

Could you please help pointing me in the right direction here?

Thank you.
Tomas

[mike60smart]

Hi Tomasa
Are you able to upload a copy of the database?