Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MyInfo (

client_id INTEGER NOT NULL,

status VARCHAR(10) NOT NULL,

start_date TIMESTAMP NOT NULL,

end_date TIMESTAMP NULL

);

INSERT INTO MyInfo

(client_id, status, start_date, end_date)

VALUES

(1, 'active', '2020-01-01', '2020-03-15'),

(1, 'inactive', '2020-03-15', null),

(2, 'active', '2020-01-01', null),

(3, 'active', '2020-01-01', '2021-04-28'),

(3, 'inactive', '2021-04-28', '2020-07-28'),

(3, 'active', '2021-07-28', null);

select client_id, extract(year from d) y, extract(month from d) m

from MyInfo t1,

lateral (select generate_series(make_date(cast(extract(year from t1.start_date) as int),

cast(extract(month from t1.start_date) as int), 1),

case when end_date is null then current_date else end_date end,

'1 month'::interval)

) Days(d)

where t1.status = 'active'

order by client_id, y, m;

CREATE TABLE

INSERT 0 6

SELECT 117