Efficient join with a "correlated" subquery

Question

Given three tables Dates(date aDate, doUse boolean), Days(rangeId int, day int, qty int) and Range(rangeId int, startDate date) in Oracle

I want to join these so that Range is joined with Dates from aDate = startDate where doUse = 1 whith each day in Days.

Given a single range it might be done something like this

SELECT rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM (
    SELECT aDate, doUse, SUM(doUse) OVER (ORDER BY aDate) day
    FROM Dates 
    WHERE aDate >= :startDAte
) INNER JOIN (
    SELECT rangeId, day,qty
    FROM Days
    WHERE rangeId = :rangeId
) USING (day)
ORDER BY day ASC

What I want to do is make query for all ranges in Range, not just one.

The problem is that the join value "day" is dependent on the range startDate to be calculated, wich gives me some trouble in formulating a query.

Keep in mind that the Dates table is pretty huge so I would like to avoid calculating the day value from the first date in the table, while each Range Days shouldn't be more than a 100 days or so.

Edit: Sample data

Dates                            Days
aDate        doUse               rangeId     day     qty
2008-01-01   1                   1           1       1
2008-01-02   1                   1           2       10
2008-01-03   0                   1           3       8
2008-01-04   1                   2           1       2
2008-01-05   1                   2           2       5

Ranges
rangeId      startDate
1            2008-01-02
2            2008-01-03


Result
rangeId      aDate        qty
1            2008-01-02   1
1            2008-01-03   0
1            2008-01-04   10
1            2008-01-05   8
2            2008-01-03   0
2            2008-01-04   2
2            2008-01-05   5

Answer 1

Ok, so maybe I've found a way. Someting like this:

SELECT irangeId, aDate + sum(case when doUse = 1 then 0 else 1) over (partionBy rangeId order by aDate) as aDate, qty
FROM Days INNER JOIN (
    select irangeId, startDate + day - 1 as aDate, qty
    from Range inner join Days using (irangeid)
) USING (aDate)

Now I just need a way to fill in the missing dates...

Edit: Nah, this way means that I'll miss the doUse vaue of the last dates...

Answer 2

Try this:

SELECT  rt.rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM    (
    SELECT *
    FROM (
     SELECT r.*, t.*, SUM(doUse) OVER (PARTITION BY rangeId ORDER BY aDate) AS span
     FROM (
      SELECT r.rangeId, startDate, MAX(day) AS dm
      FROM Range r, Days d
      WHERE d.rangeid = r.rangeid
      GROUP BY
       r.rangeId, startDate
      ) r, Dates t
     WHERE t.adate >= startDate
     ORDER BY
      rangeId, t.adate
     )
    WHERE
     span <= dm
    ) rt, Days d
WHERE   d.rangeId = rt.rangeID
    AND d.day = GREATEST(rt.span, 1)

P. S. It seems to me that the only point to keep all these Dates in the database is to get a continuous calendar with holidays marked.

You may generate a calendar of arbitrary length in Oracle using following construction:

SELECT :startDate + ROWNUM
FROM   dual
CONNECT BY
       1 = 1
WHERE  rownum < :length

and keep only holidays in Dates. A simple join will show you which Dates are holidays and which are not.