Hi,
WIN10 LO 6.4.6 HSQL 2.51
How does one do a running total with columns date, in, out, balance being the running total ?
Thanks
Here is a rough mock up of daily transactions, the query calculates the daily balance and the daily balance should be balance of day prior plus credit minus debit, in other words tell me how much money is there on any given day.
Hello,
Question is not clear. Second look tells me it is an SQL request (not initial reaction of report) but it is too general. What exactly do you want a running total of? Certainly not a date! Balance? Running total of all balances? Balances by date? What does in and out have to do with all this?
oops, sorry trying to put a cashflow model together like I did in VisiCalc and Lotus123 a long time ago. Basically I have a transaction date, description, debit and credit field and the calculated balance which I want to turn into a running total enabling the table be functional for actuals and forecasted values…
Sorry, still not clear to me. Isn’t balance a running total? May be best if you provide an actual example of what you have and are looking for.
Hello,
You have a bad habit of leaving NULL values in numeric fields. Best to default to a value (zero).
Don’t know what exactly the output wanted is (you can always add what you want), but here is a running balance with id & date fields:
SELECT T1."id", T1."date", SUM(T2."credit" - T2."debit") "Running_Total" FROM "tblcashflow" T1
INNER JOIN "tblcashflow" T2
ON T1."id" >= T2."id"
GROUP BY T1."id"
ORDER BY T1."date"
with this as a result:
Other databases have window (analytic) functions making this much easier but could not find in HSQLDB.
@Ratslinger
Thanks for that ! Yes got lots of bad habits -mea culpa. Am considering switching to Postgres as much more functionality Windows Function, Pivots, etc and I just love the quality of the documentation and tutorials. Only trouble is, its not as portable and needs a little server admin, starting, stopping some of which can be dealt with good old batch files.
just had one of those oops experiences. Not sure if what I want to accomplish is actually possible.
The running total works beautifully, only trouble starts when data is not entered chronologically,
which would be the usual case in a forecasting model where you wack in 12 month worth of mortgage payments,
quarterly utility bills… then sort it all nicely by dates and you will know you are gonna be short somewhere down the line.
That works fine in a spreadsheet as CALC does not care about the pk.
Just eliminating the id from the query wont work as there might be multiple entries for a day.
Is there a solution ? Would an extra time field default work ?
Thanks for your thoughts
Replace ID with ‘year’ and ‘month’ of “date”.
Basically if your date = 14/08/19 the you need extracted field of ‘1908’ to replace ID which is the controlling factor in the join.
Thank you kindly, now brushing up on functions, getting there,
After some rest, did quick test. Using the existing date field should work:
SELECT T1."id", T1."date", T1."balance", SUM(T2."balance") "Running_Total" FROM "transaction2" T1
INNER JOIN "transaction2" T2
ON T1."date" >= T2."date"
GROUP BY T1."id",T1."date",T1."balance"
ORDER BY T1."date"Hi, really appreciate your kindness, time and help - think almost there - only problem T2.balance does not exist, its the calculation of SUM(T2.“credit” - T2.“debit”)
cheers from a rainy freezing South of Chile, hope your climate is better !
Sorry for the delay and confusion. Have too many samples and tests laying around. And no excuse but was in the middle of an email question (Base reports).
Did just test this:
SELECT T1."id", T1."date",T1."credit" - T1."debit", SUM(T2."credit" - T2."debit") "Running_Total" FROM "tblcashflow" T1
INNER JOIN "tblcashflow" T2
ON T1."date" >= T2."date"
GROUP BY T1."id"
ORDER BY T1."date"No need for sorry, you are a very generous person, helping a lot of people voluntarily, providing
valuable expertise and solutions for which others might charge an arm and a leg. In the past one had to buy stacks of books every time a new version of some application was released…
All looking good,
Balance difference between id 1 and 2 is 172.2 which is the sum of 2,3,4,5
so whenever there are multiple transactions on the same day the balance is the
same for all of them.
Now changing the date 16/2 Bendigo to 3/1 and refresh - all ok
Adding some Mortgage pmts for Feb/Mar/Apr at bottom and a couple of dividends
for Jan/Feb and refresh -all clear
Bit of filtering - oops, can not filter on the right because read only, ok
All fantastic
Thank you very much!