Query which will give a list of dates between two date ranges

Question

My question is similar to following question:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14582643282111

The difference is my inner query returns two records and I have outer query.

I need to write inner query something like this which will give me a list of dates between two date ranges (I am trying this query whcih does not execute).

Select * from outerTable where  my_date in
(   
    select to_date(r.REQ_DATE) + rownum -1
     from all_objects, (MY_INNER_QUERY Where ID =100) r 
     where rownum <= to_date(r.DUE_DATE,'dd-mon-yyyy')-to_date(r.REQ_DATE,'dd-mon-yyyy')+1;
)

My inner query returns following 2 rows:

Select * from innerTable Where ID =100


    Start date           end date 
    3/19/2013            3/21/2013 
    3/8/2013             3/8/2013

So I am need inner query which will return following dates to outer query:

    3/19/2013
    3/20/2013
    3/21/2013
    3/8/2013

Answer 1

Great question - this one really drew me in! The answer was more or less buried further down on Tom's post. Here's the short version, using a table called TestDR to define the ranges. First the TestDR contents:

SELECT * FROM TestDR;

STARTDATE ENDDATE
--------- ---------
19-MAR-13 21-MAR-13
08-MAR-13 08-MAR-13

Now for the query to create one row for each date in the range:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= 60
)
SELECT StartDate + DaysToAdd TheDate
FROM TestDR
CROSS JOIN NUMS
WHERE TestDR.EndDate - TestDR.StartDate + 1 > DaysToAdd
ORDER BY 1

THEDATE
---------
08-MAR-13
19-MAR-13
20-MAR-13
21-MAR-13

With the query adapted from Tom's posting, you have to know the maximum range going in to "seed" the NUMS query. He used 60 in his example so that's what I used above. If you don't think any row from your subquery will ever have a range of more than 60 days then this will do the trick. If you think the maximum could be as much as 1000 days (about three years) then change the 60 to 1000. I tried this and queried a 2 1/2 year range and the results were instantaneous.

If you want to specify the exact "seed" count you can calculate it if you're willing to make the query a bit more complicated. Here's how I can do it with my TestDR table:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= (
    SELECT MAX(EndDate - StartDate + 1)
    FROM TestDR)
)
SELECT ... and the rest of the query as above

Answer 2

For your problem, you don't need to enumerate the dates. A simple JOIN suffices.

SELECT o.*
  FROM outerTable o
 INNER JOIN innerTable i
    ON i.ID = 100
   AND o.my_date BETWEEN i.REQ_DT and i.DUE_DT

From your code, I can tell that you must be a OO programmer and not familiar with SQL. It does a lot for you, so don't try to control it. It will hinder it's optimization features.

Don't take this in the wrong way, I had the same mindset (believing that I am smarter than the machine).