SQL: Generate Record Per Month for 1st of every month In Date Range

Question

I have below source table - TEST_TAB

DP_AD_ACCT_NBR	DP_AD_CCY_CDE	DP_AD_CURR_BAL	DP_AD_LST_MDFSN_DATE
10001	REL123	100	2014-11-18
10001	REL123	174	2018-03-04
10001	REL123	145	2022-12-21
10001	REL123	150	2022-12-26
10001	REL123	96	2023-01-01
10001	REL123	80	2023-01-04

I want to print transaction for 1st of each month until next transaction happens, output should look like below

DP_AD_ACCT_NBR	DP_AD_CCY_CDE	DP_AD_CURR_BAL	DP_AD_LST_MDFSN_DATE
10001	REL123	100	2014-11-18
10001	REL123	100	2014-12-01
10001	REL123	100	2015-01-01
10001	REL123	100	2015-02-01
...	...	...	...
10001	REL123	100	2018-03-01
10001	REL123	174	2018-03-04
10001	REL123	174	2018-04-01
...	...	...	...
10001	REL123	174	2022-11-01
10001	REL123	174	2022-12-01
10001	REL123	145	2022-12-21
10001	REL123	150	2022-12-26
10001	REL123	96	2023-01-01
10001	REL123	80	2023-01-04

I tried with below query but not getting expected answer

WITH TMP_TAB
  AS (SELECT DP_AD_ACCT_NBR, DP_AD_CCY_CDE,DP_AD_CURR_BAL, 
      TO_DATE(DP_AD_LST_MDFSN_DATE, 'MM/DD/YY') AS START_DATE, 
       LEAD(DP_AD_LST_MDFSN_DATE) OVER (PARTITION BY T.DP_AD_ACCT_NBR,T.DP_AD_CCY_CDE ORDER BY DP_AD_LST_MDFSN_DATE)AS END_DATE 
       FROM TEST_TAB
     )
SELECT DP_AD_ACCT_NBR, DP_AD_CCY_CDE,DP_AD_CURR_BAL,
     , TO_CHAR(ADD_MONTHS(start_date, level -1), 'MONTH') AS NAME_MONTH
     , TO_CHAR(ADD_MONTHS(start_date, level -1), 'YYYY')  AS NAME_YEAR
  FROM TMP_TAB
 CONNECT BY level <=  TO_NUMBER(TO_CHAR(end_date, 'MM') - TO_CHAR(start_date, 'MM'))            -- Month Difference
                    + 12 * TO_NUMBER((TO_CHAR(end_date, 'YYYY') - TO_CHAR(start_date, 'YYYY'))) -- Year Difference
                    + 1

Answer 1

You can use a recursive query:

WITH generate_months (
  DP_AD_ACCT_NBR,
  DP_AD_CCY_CDE,
  DP_AD_CURR_BAL,
  DP_AD_LST_MDFSN_DATE,
  next_date
) AS (
  SELECT t.*,
         LEAD(DP_AD_LST_MDFSN_DATE)
           OVER (
             PARTITION BY DP_AD_ACCT_NBR, DP_AD_CCY_CDE
             ORDER BY DP_AD_LST_MDFSN_DATE
           )
  FROM   test_tab t
UNION ALL
  SELECT DP_AD_ACCT_NBR,
         DP_AD_CCY_CDE,
         DP_AD_CURR_BAL,
         ADD_MONTHS(TRUNC(DP_AD_LST_MDFSN_DATE, 'MM'), 1),
         next_date
  FROM   generate_months
  WHERE  ADD_MONTHS(TRUNC(DP_AD_LST_MDFSN_DATE, 'MM'), 1) < next_date
)
SEARCH DEPTH FIRST BY DP_AD_ACCT_NBR, DP_AD_CCY_CDE, DP_AD_LST_MDFSN_DATE
  SET order_id
SELECT DP_AD_ACCT_NBR,
       DP_AD_CCY_CDE,
       DP_AD_CURR_BAL,
       DP_AD_LST_MDFSN_DATE
FROM   generate_months;

or a LATERAL join to a hierarchical query:

SELECT t.DP_AD_ACCT_NBR,
       t.DP_AD_CCY_CDE,
       t.DP_AD_CURR_BAL,
       m.day AS DP_AD_LST_MDFSN_DATE
FROM   ( SELECT t.*,
                LEAD(DP_AD_LST_MDFSN_DATE) OVER (
                  PARTITION BY DP_AD_ACCT_NBR, DP_AD_CCY_CDE
                  ORDER BY DP_AD_LST_MDFSN_DATE
                ) AS next_date
         FROM   test_tab t
       ) t
       CROSS JOIN LATERAL (
         SELECT GREATEST(
                  DP_AD_LST_MDFSN_DATE,
                  ADD_MONTHS(TRUNC(DP_AD_LST_MDFSN_DATE, 'MM'), LEVEL - 1)
                ) AS day
         FROM   DUAL
         CONNECT BY LEVEL <= MONTHS_BETWEEN(next_date, DP_AD_LST_MDFSN_DATE) + 1
       ) m

Which, for the sample data:

CREATE TABLE test_tab (DP_AD_ACCT_NBR, DP_AD_CCY_CDE, DP_AD_CURR_BAL, DP_AD_LST_MDFSN_DATE) AS
SELECT 10001, 'REL123', 100, DATE '2014-11-18' FROM DUAL UNION ALL
SELECT 10001, 'REL123', 174, DATE '2018-03-04' FROM DUAL UNION ALL
SELECT 10001, 'REL123', 145, DATE '2022-12-21' FROM DUAL UNION ALL
SELECT 10001, 'REL123', 150, DATE '2022-12-26' FROM DUAL UNION ALL
SELECT 10001, 'REL123',  96, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 10001, 'REL123',  80, DATE '2023-01-04' FROM DUAL;

Both output:

DP_AD_ACCT_NBR	DP_AD_CCY_CDE	DP_AD_CURR_BAL	DP_AD_LST_MDFSN_DATE
10001	REL123	100	2014-11-18 00:00:00
10001	REL123	100	2014-12-01 00:00:00
10001	REL123	100	2015-01-01 00:00:00
10001	REL123	100	2015-02-01 00:00:00
...	...	...	...
10001	REL123	100	2018-02-01 00:00:00
10001	REL123	100	2018-03-01 00:00:00
10001	REL123	174	2018-03-04 00:00:00
10001	REL123	174	2018-04-01 00:00:00
...	...	...	...
10001	REL123	174	2022-11-01 00:00:00
10001	REL123	174	2022-12-01 00:00:00
10001	REL123	145	2022-12-21 00:00:00
10001	REL123	150	2022-12-26 00:00:00
10001	REL123	96	2023-01-01 00:00:00
10001	REL123	80	2023-01-04 00:00:00

fiddle