I’ve found a few answers that can get me close here and here, but not exactly what I’m looking for.
Here’s the sample data set
x
CREATE TABLE invoices
(
"start" date,
"end" date,
custid int,
amt float
);
INSERT INTO invoices
VALUES ('2021-02-01', '2022-01-31', 1, 10),
('2021-03-01', '2021-03-31', 2, 20)
;
select * from invoices
start | end | custid | amt |
---|---|---|---|
2021-02-01 | 2022-01-31 | 1 | 10 |
2021-03-01 | 2021-03-31 | 2 | 20 |
The ideal output would generate the series for each customer like the table below
month | custid | amt |
---|---|---|
2021-02-28 | 1 | 10 |
2021-03-31 | 1 | 10 |
2021-04-30 | 1 | 10 |
2021-05-31 | 1 | 10 |
2021-06-30 | 1 | 10 |
2021-07-31 | 1 | 10 |
2021-08-31 | 1 | 10 |
2021-09-30 | 1 | 10 |
2021-10-31 | 1 | 10 |
2021-11-30 | 1 | 10 |
2021-12-31 | 1 | 10 |
2022-01-31 | 1 | 10 |
2021-03-31 | 2 | 20 |
I’m assuming this will require the generate_series
function, but quite sure how to proceed.
Edit: spelled out MRR in the title so we’re all on the same page.
Edit 2: The code block below seems to work, but still very interested in other approaches that might be more efficient
CREATE TABLE invoices
(
"start" date,
"end" date,
custid int,
amt float
);
INSERT INTO invoices
VALUES ('2021-02-01', '2022-01-31', 1, 10),
('2021-03-01', '2021-03-31', 2, 20)
;
WITH cal AS ( -- calender table
SELECT generate_series('2021-02-01'::date, '2022-01-31'::date , '1 month'::interval)::date dt
)
SELECT
a.custid, a.amt
,a.start
,a.end
,c.dt
FROM cal c
CROSS JOIN invoices a
where a.start <= c.dt and a.end >=c.dt
order by a.custid, c.dt
;
Advertisement
Answer
Use generate_series
to create a table of months:
SELECT (s.d + '1 month')::date - 1 AS month,
i.custid,
i.amt
FROM invoices AS i
CROSS JOIN LATERAL generate_series(
date_trunc('month', i.start::timestamp),
i.end::timestamp,
'1 month'
) AS s(d)
ORDER BY i.custid, s.d;
month │ custid │ amt
════════════╪════════╪═════
2021-02-28 │ 1 │ 10
2021-03-31 │ 1 │ 10
2021-04-30 │ 1 │ 10
2021-05-31 │ 1 │ 10
2021-06-30 │ 1 │ 10
2021-07-31 │ 1 │ 10
2021-08-31 │ 1 │ 10
2021-09-30 │ 1 │ 10
2021-10-31 │ 1 │ 10
2021-11-30 │ 1 │ 10
2021-12-31 │ 1 │ 10
2022-01-31 │ 1 │ 10
2021-03-31 │ 2 │ 20
(13 rows)