Incremental and decremental count based on a date

[koimoi]

Name Start_date end_date
aaa 01/02/2017 05/03/2017
bbb 03/05/2017 07/07/2017
ccc 02/01/2017 10/09/2017
I want to write a query that calculates the number of people who exist in
the DB in a certain month/year.

Answer:

Jan 2017 1
Feb 2017 2
Mar 2017 3
Apr 2017 3
May 2017 2 (one person - aaa ,ended in May 2017)
Jun 2017 2
Jul 2017 1 (bbb ended in July 2017)
How do I write a PSQL query to get the desired output?

Thanks.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html

[David G. Johnston]

Post by koimoi
Name Start_date end_date
aaa 01/02/2017 05/03/2017
bbb 03/05/2017 07/07/2017
ccc 02/01/2017 10/09/2017
I want to write a query that calculates the number of people who exist in
the DB in a certain month/year.
Jan 2017 1
Feb 2017 2
Mar 2017 3
Apr 2017 3
May 2017 2 (one person - aaa ,ended in May 2017)
Jun 2017 2
Jul 2017 1 (bbb ended in July 2017)
How do I write a PSQL query to get the desired output?

The generate_series and to_char functions will get you a table containing
all the desired year/month values - and should include the "last day" of
each the month. You can join that table against the data above:

year_month LEFT JOIN data ON (year_month.last_day_of_month BETWEEN
data.start_date AND data.end_date)

Then just GROUP BY and count(name)

You'd use "first_day_of_month" if you want to count partial months,
computing the last day of a month is trickier but doable (usually one
computes the first day of the next month and then subtracts one day).

David J.

[koimoi]

Thank you. I am PSQL Newbie, would you be able to give me the query though?

Thanks



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html