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
enddate in the outer query's join.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.