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

MT0 · Accepted Answer · 2023-02-02T15:34:54.150

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

@ MT0 Thanks for the quick reply, but there is small change in problem statement, currently we are printing dates up to last transaction date. now the change is I want to print the transaction date for the 1st of each month up to the current month. Suppose I have data in my source table to the date '2022-12-26' only, as per current logic it will print transactions up to '2022-12-26' but I want 1 row added for 1st Jan 2023 as January is the current month. — , Jan 13 '23 at 05:20
@SQLLearner `... LEAD(DP_AD_LST_MDFSN_DATE, 1, SYSDATE) OVER ...` — MT0, Jan 13 '23 at 09:06
@SQLLearner Your columns are all abbreviations that are effectively random letters; I have not idea what "actual transaction date" means or what you have tried. What you are asking appears to be outside the scope of the original question so it would be better to ask a new question with a [MRE] (rather than trying to expand the question beyond its original scope in comments). — MT0, Jan 16 '23 at 12:25
@ MT0 Is there any other way to implement this instead of recursive CTE? — , Feb 02 '23 at 14:08
@SQLLearner There are many other ways. I've added another option. — MT0, Feb 02 '23 at 15:35

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

1 Answers1