| Mytable.date | caldates.current_period_std | Caldates.next_period_std | Header 4 |
|---|
| 30/01/2020 | 01/01/2020 | 31/01/2020 | |
| 14/02/2020 | 02/02/2020 | 26/02/2020 | |
I have a table from which i query a dates : select date1 from mytable
For each one of these dates I want to determine a current_date_period and next_date_Period from another table that is a calendar table that contains 4 weekly period dates in rows.
I can do this in separate statements and loop through but I would like to combine it into a single statement.
I have this below, but want to improve it, as I am looping and hitting the calendar table twice each loop. I have tried With Select, lead and lag statements but none have worked.
Declare
cursor c1 is
select date1 from mytable;
p_current_period_std date;
p_next_period_std date;
Begin
FOR c1_rec IN c1
Loop
SELECT MAX (caldate) into p_current_period_std
FROM caldates
WHERE caldate <=c1_rec.date1;
SELECT min (caldate) into p_next_period_std
FROM caldates
WHERE caldate >= c1_rec.date1;
-- Then do some updates based on date1, p_current_period_std and p_next_period_std
end loop;
End;