Re: Incremental and decremental count based on a date

On Tue, Feb 20, 2018 at 2:11 PM, koimoi <s(dot)raddy(dot)89(at)gmail(dot)com> wrote:

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