Re: Reporting by family tree

Thank you David and Swastik, Swastik, I will work on it and will let you
know.
Thanks again for your help

On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <gurung_swastik(at)yahoo(dot)com>
wrote:

> Example Below:
>
> -- create a test family table
> create table family as
> (
> select
> 1 as id,
> null::integer as parent_id,
> 'Grandfather1' as name
> union all
> select
> 2 as id,
> null::integer as parent_id,
> 'Grandfather2' as name
> union all
> select
> 3 as id,
> 1 as parent_id,
> 'Father1-1' as name
> union all
> select
> 4 as id,
> 1 as parent_id,
> 'Father1-2' as name
> union all
> select
> 5 as id,
> 2 as parent_id,
> 'Father2-1' as name
> union all
> select
> 6 as id,
> 3 as parent_id,
> 'Son1-1-1' as name
> union all
> select
> 7 as id,
> 4 as parent_id,
> 'Son1-2-1' as name
> union all
> select
> 8 as id,
> 5 as parent_id,
> 'Son2-1-1' as name);
>
> -- create a test contribution table
> create table contribution as
> (
> select
> 1 as contributor_id,
> '2020-01-01' as date,
> 300.00 as contribution_amount
> union all
> select
> 1 as contributor_id,
> '2020-02-01' as date,
> 255.00 as contribution_amount
> union all
> select
> 1 as contributor_id,
> '2020-03-01' as date,
> 45.65 as contribution_amount
> union all
> select
> 2 as contributor_id,
> '2020-05-01' as date,
> 22.55 as contribution_amount
> union all
> select
> 2 as contributor_id,
> '2020-01-01' as date,
> 450.00 as contribution_amount
> union all
> select
> 3 as contributor_id,
> '2020-02-01' as date,
> 200.00 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-03-01' as date,
> 150.00 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-04-01' as date,
> 60.45 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-05-01' as date,
> 300.00 as contribution_amount
> union all
> select
> 5 as contributor_id,
> '2020-06-01' as date,
> 1250.00 as contribution_amount
> union all
> select
> 6 as contributor_id,
> '2020-01-01' as date,
> 66.50 as contribution_amount
> union all
> select
> 7 as contributor_id,
> '2020-02-01' as date,
> 855.00 as contribution_amount
> union all
> select
> 8 as contributor_id,
> '2020-02-01' as date,
> 25.00 as contribution_amount);
>
> -- execute recursive query, all children inheriting contribution sum of
> parents
> with recursive cte as
> (
> select
> f.id,
> f.parent_id,
> f.name,
> c.contribution_amount
> from
> family f
> join contribution c on
> f.id = c.contributor_id
> union all
> select
> f.id,
> f.parent_id,
> f.name,
> cte.contribution_amount
> from
> cte
> join family f on
> cte.id = f.parent_id)
> select
> id,
> name,
> sum(contribution_amount) as total_contribution
> from
> cte
> group by
> id,
> name
> order by
> id;
>
> -- execute recursive query, parents have sum of all contributions of its
> children
> with recursive cte as
> (
> select
> f.id,
> f.parent_id,
> f.name,
> c.contribution_amount
> from
> family f
> join contribution c on
> f.id = c.contributor_id
> union all
> select
> f.id,
> f.parent_id,
> f.name,
> cte.contribution_amount
> from
> cte
> join family f on
> cte.parent_id = f.id)
> select
> id,
> name,
> sum(contribution_amount) as total_contribution
> from
> cte
> group by
> id,
> name
> order by
> id;
>
>
> Change your SQL accordingly. Also, you can add month field to yield
> results per month.
>