Q
Problem solve Get help with specific problems with your technologies, process and projects.

Aggregates for date periods

A member asks our SQL expert: "How can I generate daily, weekly, monthly, quarterly, and yearly reports based on this sample data..."

How can I generate daily, weekly, monthly, quarterly, and yearly reports based on this sample data:

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.


This was last published in October 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close