-
Hi,
I have a table with 3 columns
Column Names: id Number
start_date Date
end_date Date
Start_Date & End_Date will contain only dates of SUNDAY's.
For Eg: ID = 1, Start_Date = 16-JUN-2002 , End_Date = 30-JUN-2002
ID = 2, Start_Date = 04-AUG-2002 , End_Date = 25-AUG-2002
Like this there will be more than one record in the table.
My requirement is to split the above record by weeks.
For the above example no. of weeks between 16-JUN-2002 & 30-JUN-2002 is 2 weeks and for 04-AUG-2002 & 24-AUG-2002 is 3 weeks.
So my query should return 2 rows for record 1 and 3 rows for record 2, the records should be split with respect to SUNDAYS as below:
Eg: ID Start_Date End_Date
1 16-JUN-2002 23-JUN-2002
1 23-JUN-2002 30-JUN-2002
2 04-AUG-2002 11-AUG-2002
2 11-AUG-2002 18-AUG-2002
2 18-AUG-2002 25-AUG-2002
This should be done with SELECT statement.
I definitely believe you should have come across this from your experience.
I appreciate your help in advance.
Thanks.
RaviSHankar.
-
Just a SELECT ? That's a brain teaser. Can you create temporary table or something ?
-
I agree with shibha:
If you create a (temp) table with all possible dates
(from min(start) till max(end) ) only the sundays,
you can join the tables like this:
select o.id, d.start_dt, d.end_dt
from org_table o
, date_table d
where d.start_dt >= o.start_dt
and d.end_dt < o.end_dt
Can be some mistakes in it, but it's the idea.
-
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>
