I have a problem to calculate parent account balances. In the database there are two tables: account and monthly_balance. In the monthly_balance table we store only leaf account balances, but I have to know the ancestors balances too. Also I have to filter by date interval but right now that is not the main issue.
I tried some solution from stackoverflow but I got null values or another faulty outcome.
example situation:
A1
+- A1.1 (6)
+- A1.2
+- A1.2.1 (1)
+- A1.2.2 (10)
+- A1.2.3 (3)
A2
+- A2.1
+- A2.1.1 (10)
+- A2.1.2 (5)
On return I should get:
A1 = 20
A1.1 = 6
A1.2 = 14
A1.2.1 = 1
A1.2.2 = 10
A1.2.3 = 3
A2 = 15
A2.1 = 15
A2.1.1 = 10
A2.1.2 = 5
data stored like
create table account(id int primary key, parent_id int, account_name text)
insert into account values
(1, null, 'A1'),
(2, 1, 'A1.1'),
(3, 1, 'A1.2'),
(4, 3, 'A1.2.1'),
(5, 3, 'A1.2.2'),
(6, 3, 'A1.2.3'),
(7, null, 'A2'),
(8, 7, 'A2.1'),
(9, 8, 'A2.1.1'),
(10, 8, 'A2.1.2');
create table monthly_balance(id int primary key, account_id int foreign key, balance numeric, year_month date)
insert into account values
(1, 2, 6, '2022-08-01'),
(2, 4, 1, '2022-08-01'),
(3, 5, 10, '2022-08-01'),
(4, 6, 3, '2022-08-01'),
(5, 9, 10, '2022-08-01'),
(6, 10, 5, '2022-08-01');
you can use recursive cte for this:
with recursive cte_account AS (
select
a.id as account_id,
a.id as balance_account_id
from account as a
where
not exists (select from account as tt where tt.parent_id = a.id)
union all
select
a.parent_id as account_id,
c.balance_account_id
from cte_account as c
inner join account as a on
a.id = c.account_id
where
a.parent_id is not null
), cte_balance as (
select
a.account_id,
sum(b.balance) as balance
from cte_account as a
inner join monthly_balance as b on
b.account_id = a.balance_account_id
group by
a.account_id
)
select
a.account_name,
b.balance
from cte_balance as b
inner join account as a on
a.id = b.account_id
order by
a.id asc