I'm writing a quick and dirty report to give to the powers that be showing just how often - or not - certain reports are run. I'm also going to include some other anecdotal information. One of the things I'd like to include are average times (rendering, data retrieval, etc.). Here's what I've got so far that works really well:

Code:
SELECT DISTINCT 

c.Name, 

c.Path, 

c.CreationDate, 

c.Description, 

COUNT(e.ReportID) AS [Number of Times Executed], 

MAX(e.TimeStart) AS [Last Time Executed],

AVG(e.[RowCount]) AS [AVG NUMBER OF ROWS],

AVG(e.timedataretrieval) AS [AVG DATA RETRIEVAL TIME],

AVG(e.timeprocessing) AS [AVG TIME PROCESSING],

AVG(e.timerendering) AS [AVG TIME RENDERING]


FROM ExecutionLog AS e INNER JOIN

Catalog AS c ON e.ReportID = c.ItemID

GROUP BY c.Name, c.Path, c.CreationDate, c.Description

ORDER BY c.Name
While this works great, there's one problem. When calculating the averages, I only want to include those executions that had a status of rsSuccess. For example, one report was run three times. One execution had an rsInternalError with a row count of half a million. The other two times succeeded and had significantly lower row counts. When calculating the averages, I'd like to throw out the "bad" executions, so as not to skew the results.

Could someone suggest how I can tweak this query to accomplish this?



Thanks!!