How to insert a new row based on previous row-CodePudding

Given table:

Name	Date	Last Date	Frequency
Person1	1-Jan-2022	2-May-2022	30 Days

Expected output:

Name	Updated Date	Some Other Column
Person1	1-Jan-2022	Value 1
Person1	31-Jan-2022	Value 2
Person1	2-Mar-2022	Value 3
Person1	1-Apr-2022	Value 4
Person1	1-May-2022	Value 5

As shown above we have been given one record, ask is to create a new table from this record. We have to keep inserting new rows till "Updated Date"(Previous row Date frequency days) in the expected table is less than "Last Date" in the given table.

I want to understand if there is a way to achieve this by Oracle SQL. (without PL/SQL).

CodePudding user response：

We can use a recursive CTE in the INSERT SELECT to generate the dates.
I have used where name = 'Person1' but this could be removed or modified as needed. If a record from table1 exists in table2 should it be updated, duplicated or nothing changed?
I have taken the liberty to make the frequency field type int for the number of days. We could change this to be able to program frequencies in days, weeks, months etc. but the incrementation in the CTE would become more complicated.
There is a dbFiddle link at the bottom.

create Table table1(
  Name varchar(25),
  FirstDate Date,
  LastDate Date,
  Frequency int);

insert into table1 values
('Person1','1-Jan-2022','2-May-2022',30);

create table table2 (
  Name varchar(25),
  UpdatedDate date,
  SomeColumn varchar(25));

INSERT INTO table2 

WITH CTE (Name, Date_, LD ) AS
(SELECT
  Name,
  FirstDate AS Date_ ,
  LastDate
FROM table1
WHERE Name = 'Person1'
UNION ALL
SELECT
  Name,
  Date_   30,
  LD
FROM CTE
WHERE Date_ <= LD)

SELECT Name, Date_, null FROM CTE;

SELECT * FROM table2;

NAME    | UPDATEDDATE | SOMECOLUMN
:------ | :---------- | :---------
Person1 | 01-JAN-22   | null      
Person1 | 31-JAN-22   | null      
Person1 | 02-MAR-22   | null      
Person1 | 01-APR-22   | null      
Person1 | 01-MAY-22   | null      
Person1 | 31-MAY-22   | null

db<>fiddle here