I want to add 1 month for loop by subscribed month to get each customers monthly payment date.

I have a table like this:

I want to have result like this:

Tried this, but returning duplicated.

SELECT ID, ADDMONTHS(REGISTER_DATE,LEVEL) FROM SUBLIST CONNECT BY LEVEL<=SUB_MONTH

Any help will be appreciated. Many thanks.

CodePudding user response：

Here's one option:

Setting date format (you don't have to do that):

SQL> alter session set nls_date_format = 'yyyy.mm.dd';

Session altered.

Sample data:

SQL> with test (id, register_date, subscribed_month) as
  2    (select 1, date '2022-01-01', 3 from dual union all
  3     select 2, date '2022-07-01', 6 from dual
  4    )

Query begins here:

  5  select id, register_date, subscribed_month,
  6    add_months(register_date, column_value - 1) must_pay_date
  7  from test cross join table(cast(multiset(select level from dual
  8                                           connect by level <= subscribed_month
  9                                          ) as sys.odcinumberlist))
 10  order by id, register_date, must_pay_date;

        ID REGISTER_D SUBSCRIBED_MONTH MUST_PAY_D
---------- ---------- ---------------- ----------
         1 2022.01.01                3 2022.01.01
         1 2022.01.01                3 2022.02.01
         1 2022.01.01                3 2022.03.01
         2 2022.07.01                6 2022.07.01
         2 2022.07.01                6 2022.08.01
         2 2022.07.01                6 2022.09.01
         2 2022.07.01                6 2022.10.01
         2 2022.07.01                6 2022.11.01
         2 2022.07.01                6 2022.12.01

9 rows selected.

SQL>

CodePudding user response：

You can inner join your data to a subquery containing rows with numbers of months to be added:

WITH
    tbl (ID, REGISTER_DATE, SUBSCRIBED_MONTH) AS
        (
            Select 1, DATE '2022-01-01', 3 From Dual Union All
            Select 2, DATE '2022-07-01', 6 From Dual
        )
SELECT      t0.ID, t0.REGISTER_DATE, t0.SUBSCRIBED_MONTH,
            Add_Months(t0.REGISTER_DATE, t1.MNTHS - 1) "PAY_DATE",
            t1.MNTHS "PAYMENT_NO"
FROM        tbl t0
INNER JOIN  ( Select DISTINCT ID, LEVEL "MNTHS" From tbl Connect By LEVEL <= SUBSCRIBED_MONTH ) t1 ON(t1.ID = t0.ID)
ORDER BY    t0.ID, t0.REGISTER_DATE, t1.MNTHS

Which with your sample data

WITH
    tbl (ID, REGISTER_DATE, SUBSCRIBED_MONTH) AS
        (
            Select 1, DATE '2022-01-01', 3 From Dual Union All
            Select 2, DATE '2022-07-01', 6 From Dual
        )

... results as ...

        ID REGISTER_DATE SUBSCRIBED_MONTH PAY_DATE  PAYMENT_NO
---------- ------------- ---------------- --------- ----------
         1 01-JAN-22                    3 01-JAN-22          1 
         1 01-JAN-22                    3 01-FEB-22          2 
         1 01-JAN-22                    3 01-MAR-22          3 
         2 01-JUL-22                    6 01-JUL-22          1 
         2 01-JUL-22                    6 01-AUG-22          2 
         2 01-JUL-22                    6 01-SEP-22          3 
         2 01-JUL-22                    6 01-OCT-22          4 
         2 01-JUL-22                    6 01-NOV-22          5 
         2 01-JUL-22                    6 01-DEC-22          6