SQL & PL/SQL

Hi,

I am struggling to find a way of using "partition by" to populate a sparse dataset with "missing" dates. All the examples i have found all supply the date range as hard-codes/user supplied. However, the min/max date range must come from the records themselves for my problem.

An an example of what i mean is as follows.

Dataset is:

Fruit Ordered Qty
--------- --------------- ------
Apples 08-Aug-10 1
Oranges 11-Aug-10 1
Pears 08-Aug-10 6
Pears 10-Aug-10 1

So, what I want to acheive is a result set that provides filll in the other sparse data for each day between the first day of the data set (ie Min(Ordered)=08-Aug-10) and the last date of the data set (Max(ordered=11-Aug))

ie.
Apples 08-Aug-10 1
Apples 09-Aug-10 0
Apples 10-Aug-10 0
Apples 11-Aug-10 0
Oranges 08-Aug-10 0
Oranges 09-Aug-10 0
Oranges 10-Aug-10 0
Oranges 11-Aug-10 1
Pears 08-Aug-10 6
Pears 09-Aug-10 0
Pears 10-Aug-10 1
Pears 11-Aug-10 0

If the above is in a table called FRUITS and I create a CALENDAR table as:

Dates
--------------
.....
01-JUL-10
02-JUL-10
03-JUL-10
04-JUL-10
05-JUL-10
etc

then I need to be able to produce the result set WITHOUT specifying the where clause : ..... "dates between '08-AUG-10' and '11-AUG-10'

These dates must be derived from the data itself as the earliest and latest date in the result set.
I've simplified my problem in this example but think it contains the the crux of the problem.


I can can't seem to do this using "PARTITION BY" . I'm trying to avoid a correlated subquery too in the answer as the real application of this will be in a table with millions of records.

Is anyone able to help solve this?

Many thanks.
Jim

Edited by: 865902 on 14-Jun-2011 15:58

Edited by: 865902 on 14-Jun-2011 16:00