The following will work for the any interval in this decade (522 weeks, from 01-JAN-2000 until 31-DEC-2009).
Code:SQL> select * from my_table; ID START_DAT END_DATE ---------- --------- --------- 1 16-JUN-02 30-JUN-02 2 04-AUG-02 25-AUG-02 SQL> SELECT a.id, b.start_date, b.end_date 2 FROM my_table a, 3 (SELECT (ROWNUM-1)*7+NEXT_DAY('01-JAN-2000','SUNDAY') start_date, 4 ROWNUM*7+NEXT_DAY('01-JAN-2000','SUNDAY') end_date 5 FROM all_objects 6 WHERE ROWNUM <= 522) b 7 WHERE b.start_date >= a.start_date 8 AND b.start_date < a.end_date; ID START_DAT END_DATE ---------- --------- --------- 1 16-JUN-02 23-JUN-02 1 23-JUN-02 30-JUN-02 2 04-AUG-02 11-AUG-02 2 11-AUG-02 18-AUG-02 2 18-AUG-02 25-AUG-02 SQL>




Reply With Quote