For LOOP in PostgreSQL

Question

I have a table with the following columns: (client_id, start_contract_date, end_contract_date)

Every client has a start_contract_date but some clients have a NULL for end_contract_date since they may still be active today.

If we check for a certain date D, a client is active if D is between start_contract_date and end_contract_date (or start_contract_date <= D, if end_contract_date is NULL.)

I want to count, for each month of each year, over 2016 until today, how many customers are active. My problem is that I do not know how to LOOP on the months and years.

I have a partial solution. I can count how many active clients for a specific month of a specific year.

SELECT  2016 as year , 7 as month, count(id_client)
FROM table
WHERE 
    EXTRACT(year from start_contract_date)<=2016
AND EXTRACT(month from start_contract_date)<=7

AND (EXTRACT(year from end_contract_date)>=2016 OR end_contract_date IS NULL)
AND (EXTRACT(month from end_contract_date)>=7 OR end_contract_date IS NULL)
;

So, how can I run a nested for loop that would be something like

FOR y IN 2016..2017

    FOR m IN 1..12

I want the output to be

Year , Month , Count
2016 , 1 , 234
2016 , 2 , 54
…
2017 , 12 , 543

klin · Accepted Answer · 2019-03-24 02:10:00Z

1

Use the function generate_series() to generate arbitrary series of months, e.g.:

select extract(year from d) as year, extract(month from d) as month
from generate_series('2017-11-01'::date, '2018-02-01', '1 month') d

 year | month 
------+-------
 2017 |    11
 2017 |    12
 2018 |     1
 2018 |     2
(4 rows)

Use the above and the function date_trunc() to extract year-month value from dates:

select extract(year from d) as year, extract(month from d) as month, count(id_client)
from generate_series('2016-01-01'::date, '2019-03-01', '1 month') d
left join my_table
    on date_trunc('month', start_contract_date) <= date_trunc('month', d)
    and (end_contract_date is null or date_trunc('month', end_contract_date) >= date_trunc('month', d))
group by d
order by d

Note also that the conditions in your query contain logical error.

answered Mar 24, 2019 at 2:10

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

For LOOP in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related