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).
