CODEWITHSUNDEEP
sqlpostgresql
user575406
user575406

Reputation: 13

For LOOP in PostgreSQL

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

Upvotes: 1

Views: 155

Answers (1)

klin
klin

Reputation: 121889

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.

Upvotes: 1

Related Questions