I have below source table - TEST_TAB

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

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

CodePudding user response：

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;

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;

Outputs:

fiddle