Getting the avg time from multible days

[Larshg]

Hi

I have a job that every day updates a tabel with the time stamp of when it finishes.

ex.

Jobname job_ended
job1 23-09-2003 07:28:28
job1 24-09-2003 07:23:50
job1 25-09-2003 10:14:49

I would like to select the avage time for when the job finnishes devided into weeks.
ex.
week42 08:23
week43 09:01


I tryed using avg - but I only want the avage time not the date.


Larshg

 

[JarlH]

SELECT week, AVG(job_end)
FROM (SELECT WEEK(job_ended) AS week,
CAST(job_ended AS TIME) AS job_end
FROM job_table)
GROUP BY week

ANSI SQL-99/SQL-2003 using the non-core feature "derived tables", except that the WEEK function is not ANSI at all.

If your DBMS doesn't support derived tables you can create a view returning the same, i.e.

CREATE VIEW job_view (week, job_end) AS
SELECT WEEK(job_ended),
CAST(job_ended AS TIME)
FROM job_table

and then just do

SELECT week, AVG(job_end) FROM job_view
GROUP BY week


Hope that helps,
Jarl