Aggregates for date periods

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin?

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.