Обсуждение: Incremental and decremental count based on a date
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
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?
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.
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