How to insert a new row based on previous row

Question

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).

Can you show us what have you done in order to resolve the problem so far.. — Barbaros Özhan, Mar 17 '22 at 06:50
I suggested doing it using PL/SQL, where we can put last date in a variable and execute a loop to keep inserting records until updated date is less than last Date, but people want it to achieve by just SQL, and that's where I don't know where to go. — Ghatothkachh, Mar 17 '22 at 06:55
@BarbarosÖzhan, Thanks for fixing the Format, This is my first post on stack-overflow. Still Learning. — Ghatothkachh, Mar 17 '22 at 06:57
you're welcome( to SO too :) ), btw, provide [a minimal and reproducible](https://stackoverflow.com/help/minimal-reproducible-example) question please — Barbaros Özhan, Mar 17 '22 at 06:57
Do you have a calendar table available? If no you'll can fake one with a connect by; generate a list of all dates that increment by 30 days each time starting with x and ending with y and cross join it to the record. If you have multiple records to do this for, use a cal table that has every date and make an inner join condition a mod 30 = 0 of the days diff between the start date and the cal table date — Caius Jard, Mar 17 '22 at 07:03
Ultimately if you can envisage a process where "I would use a loop, I would have some variable X that increments by Y each time and I would modify date Z with that X" you can do the same with sql by generating a list of every number starting with N, incrementing by Y. You call the column of numbers X and the set of numbers in the column is thus the same set of numbers the loop would experience in its X variable. When you connect your data to the numbers it is the same as referencing your data in the loop with each value of X, do you see? — Caius Jard, Mar 17 '22 at 07:05
In SQL you do loops (iterations) with recursive queries. This is what this task is about, it seems. — Thorsten Kettner, Mar 17 '22 at 07:57

score 0 · Accepted Answer · 2022-03-17T08:00:35.253

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

How to insert a new row based on previous row

1 Answers1