Hi All,

I want to create a query that will shows, how long a report took to run the last time it was ran, and what is the avg report time (based on history) for that report.

1. I want to select all reports that have a status of running or waiting in the queue, and determine how they ran in the past.
2. For each running/waiting report calculate the last report process time. SUM(start_Time-Stop_Time)
3. For each running/waiting report calculate the avg report process time AVG(start_Time-Stop_Time)

NOTE: There is no end time for running/waiting reports. I need to check the report that have a complete status, and a end time to calculate avg time, and how long it took to run, the last time it was ran.

Sample data in the report queue as of Oct-225-2010
Start Time User Name Report_Desc Status
2010-10-25 09:20:54.017 tom.jones@test.com Ad-Hoc - Report1 running
2010-10-25 09:21:43.52 tom.jones@test.com Ad-Hoc - Report1 running
2010-10-25 09:24:22.986 test.one@test.com Ad-Hoc - Report2 waiting
2010-10-25 09:24:42.872 test.one@test.com Ad-Hoc - Report3 waiting
2010-10-25 09:26:01.275 user1@test.com Ad-Hoc - Report4 waiting
2010-10-25 09:26:20.337 user2@test.com Ad-Hoc - Report4 waiting


My end results would be...

User Name Report Desc LastRunTime AvgRunTime
tom.jones@test.com Ad_Hoc - Report1 2010-10-21 0hr:54m 1h:13m
tom.jones@test.com Ad_Hoc - Report1 2010-10-21 0hr:54m 1h:13m
test.one@test.com Ad_Hoc - Report2 2010-10-12 2hr:54m 3h:13m
user1@test.com Ad_Hoc - Report3 2010-10-02 0hr:20m 0h:18m
user2@test.com Ad_Hoc - Report4 2010-10-02 0hr:35m 0h:54m


I know the following will work separately, but not sure if I can join them together without writing a program.

-- Pull all reports waiting, running
SELECT user_id,report_desc,status
FROM report_hist
WHERE status IN ('waiting','running')
ORDER BY ts DESC

--Avg Time Report Ran Need: need to check each report_desc results from the first query. Not sure how to link the two together, using a LIKE statement without specify a specific value.

SELECT AVG(start_time-stop_time) AS avgtime
FROM report_hist
WHERE report_desc like 'Ad-Hoc - Report1' AND
((start_time IS NOT NULL) OR (stop_time IS NOT NULL))

-- Lasttime Report ran
SELECT FIRST 1 SUM(start_time-stop_time) AS lastruntime
FROM report_hist
WHERE report_desc like 'Ad-Hoc - Report1' AND
((start_time IS NOT NULL) OR (stop_time IS NOT NULL))
ORDER BY 1 DESC

I'd appreciate if anyone can tell me if this can be done in a query.

Thanks.

Matt