title: Efficient join with a "correlated" subquery
body: <p>Given three tables Dates(date aDate, doUse boolean), Days(rangeId int, day int, qty int) and Range(rangeId int, startDate date) in Oracle</p>
  
  <p>I want to join these so that Range is joined with Dates from aDate = startDate where doUse = 1 whith each day in Days.</p>
  
  <p>Given a single range it might be done something like this</p>
  
  <pre><code>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 &gt;= :startDAte
  ) INNER JOIN (
      SELECT rangeId, day,qty
      FROM Days
      WHERE rangeId = :rangeId
  ) USING (day)
  ORDER BY day ASC
  </code></pre>
  
  <p>What I want to do is make query for all ranges in Range, not just one.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Edit: Sample data</p>
  
  <pre><code>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
  </code></pre>