generate dates on monthly level in Oracle SQL

here is the data

CREATE TABLE table_name (a, b, c, ym) AS
SELECT 1, 2,  1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 1, 2,  7, DATE '2024-09-01' FROM DUAL UNION ALL
SELECT 2, 2,  8, DATE '2024-04-01' FROM DUAL;

I need to generate all missing dates at monthly level with columns c as Zero value. generate missing months for each unique combonation of A and B columns in those years where there is at least one month with value. for example the combination 2 and 2 has data in year 2024 so there is no need to generate months in 2023 but in 2024 only !

the output will be as follows

a     b      c   ym
1     2      1   2023-01-01
1     2      0   2023-02-01
.
.
.
.
1     2      0   2023-12-01
1     2      0   2024-01-01
.
.
.
1     2      7   2024-09-01
.
.
.
1     2      0   2024-12-01
2     2      0   2024-01-01
2     2      0   2024-02-01
2     2      0   2024-03-01
2     2      8   2024-04-01
.
.
.
2     2      0   2024-12-01

Solution

Split the date into year and month components and then use a PARTITIONed OUTER JOIN:

SELECT t.a,
       t.b,
       COALESCE(t.c, 0) AS c,
       ADD_MONTHS(t.year, c.month - 1) AS ym
FROM   (
         SELECT LEVEL AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN (
         SELECT a, b, c, TRUNC(ym, 'YY') AS year, EXTRACT(MONTH FROM ym) AS month
         FROM   table_name
       )t
       PARTITION BY (t.a, t.b, t.year)
       ON (t.month = c.month)

Which, for the sample data:

CREATE TABLE table_name (a, b, c, ym) AS
SELECT 1, 2,  1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 1, 2,  7, DATE '2024-09-01' FROM DUAL UNION ALL
SELECT 2, 2,  8, DATE '2024-04-01' FROM DUAL;

Outputs:

A	B	C	YM
1	2	1	2023-01-01 00:00:00
1	2	0	2023-02-01 00:00:00
1	2	0	2023-03-01 00:00:00
1	2	0	2023-04-01 00:00:00
1	2	0	2023-05-01 00:00:00
1	2	0	2023-06-01 00:00:00
1	2	0	2023-07-01 00:00:00
1	2	0	2023-08-01 00:00:00
1	2	0	2023-09-01 00:00:00
1	2	0	2023-10-01 00:00:00
1	2	0	2023-11-01 00:00:00
1	2	0	2023-12-01 00:00:00
1	2	0	2024-01-01 00:00:00
1	2	0	2024-02-01 00:00:00
1	2	0	2024-03-01 00:00:00
1	2	0	2024-04-01 00:00:00
1	2	0	2024-05-01 00:00:00
1	2	0	2024-06-01 00:00:00
1	2	0	2024-07-01 00:00:00
1	2	0	2024-08-01 00:00:00
1	2	7	2024-09-01 00:00:00
1	2	0	2024-10-01 00:00:00
1	2	0	2024-11-01 00:00:00
1	2	0	2024-12-01 00:00:00
2	2	0	2024-01-01 00:00:00
2	2	0	2024-02-01 00:00:00
2	2	0	2024-03-01 00:00:00
2	2	8	2024-04-01 00:00:00
2	2	0	2024-05-01 00:00:00
2	2	0	2024-06-01 00:00:00
2	2	0	2024-07-01 00:00:00
2	2	0	2024-08-01 00:00:00
2	2	0	2024-09-01 00:00:00
2	2	0	2024-10-01 00:00:00
2	2	0	2024-11-01 00:00:00
2	2	0	2024-12-01 00:00:00

fiddle