Aggregates for date periods

TestDate    Item  Result
--------    ----  ------
APR-1-2007  ABC   FAILED
APR-2-2007  DEF   PASSED
APR-3-2007  GHI   FAILED
...

The query should calculate the total number of FAILED and PASSED for a certain period (e.g. daily, weekly...), such as:

Period (Monthly)            Failure Rate (%)
------                      -------
AUG-10-2007 to SEP-10-2007  2.05
SEP-10-2007 to OCT-10-2007  1.80
OCT-10-2007 to NOV-10-2007  5.56
...

One easy way to generate the date ranges for the desired periods is with date arithmetic using a starting date and an integers table.

Let's say we need to see 12 monthly periods, starting with AUG-10-2007. Assuming our integers table has the ten numbers 0 through 9, the query to generate the ranges would be:

select '2007-08-10' + 
         interval 10*tens.i + units.i month  as startdate
     , '2007-08-10' + 
         interval 10*tens.i + units.i + 1 month  as enddate
  from integers tens
cross
  join integers units
 where 10*tens.i + units.i between 0 and 11
order 
    by startdate

Now we simply use a LEFT OUTER JOIN to the data:

select startdate || ' to ' || enddate 
          as period
     , case when count(TestDate) = 0 
            then null 
            else 
       100.0 
       * count(case when Result='FAILED' then 1 end) 
       / count(TestDate)  end
          as FailureRate
  from (
       select '2007-08-10' + 
                interval 10*tens.i + units.i month  
                   as startdate
            , '2007-08-10' + 
                interval 10*tens.i + units.i + 1 month  
                   as enddate
         from integers tens
       cross
         join integers units
        where 10*tens.i + units.i between 0 and 11
       ) as periods
left outer
  join yourtable
    on yourtable.TestDate >= periods.startdate
   and yourtable.TestDate  < periods.enddate
group 
    by period

Notice how we've pushed the cross join down into a subquery in order to be able use the calculated column aliases startdate and enddate in the outer query's join.