capturing counts of open items between dates
In looking at your query, I think there may be a few things you can do to improve your performance.  The first is that you can aggregate your ticket_table ahead of time based on the request interval. Second, unless it is needed for the logic that you redacted, I would avoid using the CTE once you start doing the real logic. You may be depriving the optomizer of information it could use.  You might try something like the following query:

WITH query_interval AS
(
  SELECT TO_DATE('09/01/2012', 'MM/DD/YYYY') AS START_DATE, TRUNC(SYSDATE) AS END_DATE
  FROM DUAL
),
interval_dates AS
(
  SELECT qi.start_date + ROWNUM - 1 AS I_DATE
  FROM query_interval qi
  CONNECT BY ROWNUM &lt; qi.end_date - qi.start_date
)
SELECT i.i_date, SUM(sub.cycle_count)
FROM interval_dates i
LEFT JOIN (SELECT GREATEST(TRUNC(tt.start_date), qi.start_date) AS OPEN_DATE,
                 LEAST(TRUNC(tt.end_date)-1, qi.end_date) AS CLOSE_DATE,
                 COUNT(*) AS CYCLE_COUNT
          FROM ticket_table tt
          CROSS JOIN query_interval qi
          WHERE GREATEST(TRUNC(tt.start_date), qi.start_date) &lt;= LEAST(TRUNC(tt.end_date)-1, qi.end_date)
          GROUP BY GREATEST(TRUNC(tt.start_date), qi.start_date), LEAST(TRUNC(tt.end_date)-1, qi.end_date)) sub ON I_DATE BETWEEN sub.open_date AND sub.close_date
GROUP BY i.i_date
ORDER BY i.i_date;


Most of this is similar to what you already had, but there are some differences.  First you notice that I added a CTE with just the interval.  This allows me to perform the aggregation that I mentioned earlier.  The aggregation works like this:


First, we limit our search to tickets which were open at some point during our query interval.  That I'm using the GREATEST &lt;= LEAST to determine an overlap between the ticket and the query interval.
We clip tickets that hang over the ends of our search interval.  You really don't care that they are open before your search, just that they are open during your search.
We aggregate all cycles which now have the same start and end dates.


After that, the logic is pretty similar to what you already had.  A left join between the full list of dates and our modified table.  Then instead of using COUNT, we use SUM.