i am making this post as an answer because i cannot see how to include an attachment in a comment.
@gkick
i saw your previous post concerning running sums but did not respond because it was marked as solved even though it was obvious that the solution did not solve your issue.
@Ratslinger
just for your information:
when using HSQL embedded (the OP did not supply back end info) it’s possible to solve this issue by using 2 simple queries.
only 1 query is required for databases which have window or recursive functions.
as you know the trick is in assigning a unique value which can be used to identify each individual record in chronological order even when date/time values are identical. the join in the setup query enables this.
EDIT: attachment not found error. have uploaded again.
New Database.odb
EDIT: 11 Sep 20
@gkick
i tested the attachment using both hsqldb 2.5.1 and postgresql 12.4 on windows 10 with LO 6.3.6.2(x64) and discovered an issue when inserting new records via the form.
’Base’ did not like the table aliases so please use the code below for the output query.
i have also posted a recursive routine, hsqldb 2.x only, it will not work with embedded.
the recursive routine is read only and not suitable as a data source for the form.
Base forms used for input can be volatile when the data source is a query which contains data from more than a single table.
calculating a running sum is expensive in terms of CPU and your database may eventually contain many thousands of records.
it’s not as convenient but may be best to use 2 forms as you have done previously.
--cash_flow_running_sum_output.sql
select "cash_flow_t".*, "q"."running_sum"
from "cash_flow_t"
join
(
select "q1"."id", sum( "q2"."total" ) "running_sum"
from "cash_flow_running_sum_setup.sql" "q1"
join "cash_flow_running_sum_setup.sql" "q2"
on "q2"."level" <= "q1"."level"
group by "q1"."id"
) "q"
on "q"."id" = "cash_flow_t"."id"
order by "cash_flow_t"."date", "cash_flow_t"."id"
--cash_flow_running_sum_recursive.sql
--shows daily transactions with a running total using recursion
--hsqldb 2.x and above. if using hsqldb.jar < 2.5.1 then replace 'union all' with 'union' (bug)
with recursive y("id", "debit", "credit", r) as
(
select y.*, rownum() r
from (select "id", "debit", "credit" from "cash_flow_t" order by "date", "id") y
),
t("id", r, "running_sum") as
(
select "id", r, coalesce("credit", 0) - coalesce("debit", 0)
from y
where r = 1
union all
select y."id", y.r, t."running_sum" + coalesce(y."credit", 0) - coalesce(y."debit", 0)
from t
join y on y.r = t.r + 1
)
select c.*, t."running_sum"
from "cash_flow_t" c
join t on t."id" = c."id"
order by c."date", c."id"
