public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ibrahim Shaame <[email protected]>
To: swastik Gurung <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Tue, 17 Oct 2023 11:18:20 +0300
Message-ID: <CAJOWwD46a+JVtnWkpNHs3ZDrEF7onrG39WryKm6X_rP=t7SHyg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAJOWwD5M=YK9nKBX6TD-NB1B7Ddo6uLZC2fG1WAED86fg4s2oA@mail.gmail.com>
	<[email protected]>
	<CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com>
	<CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com>
	<CAJOWwD6ED658ZAg80YK85sFq=vObnrj6f086tNug6C8=K7wn3w@mail.gmail.com>
	<CAKFQuwYn6MfTyjpDraVqW7TfnCh2Q3-DvfEYHz+vifXvzpr_FA@mail.gmail.com>
	<[email protected]>

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 <[email protected]>
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.
>

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Reporting by family tree
  In-Reply-To: <CAJOWwD46a+JVtnWkpNHs3ZDrEF7onrG39WryKm6X_rP=t7SHyg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox