SQL to get a job start time and end time??

[w1dge]

Hi,

I have a database that tracks jobs as they are being completed.

I have 2 tables, the first of which contains details of the jobs and the second contains details of the individual tasks that make up each job (Job and Task respectively)

Within the Job table, there are several fields, however the fields I am concerned with are JobKey, JobID, Start_Time and End_Time.

Currently, as each task within a job is completed, a new entry is added to the Job table with an updated End_Time. At the end of a job, there will be several entries in the Job table similar to

JobKey JobID Start_Time End_Time
1 1 10:00:02 11:00:07
2 1 10:00:02 11:01:14
3 2 10:02:12 11:24:45
4 1 10:00:02 11:02:34
5 1 10:00:02 11:05:25

etc

When I query the Job table, I end up with multiple references to the same job, where all I really want is a single reference to a job with the JobID, Start_Time and the End_Time the last entry relating to a particular job.

How would I write a query to give me a single reference to a JobID Start_Time and the final entry for End_Time?

eg From the above, my query should return

JobID Start_Time End_Time
1 10:00:02 11:05:25
2 10:02:12 11:24:45

Thanks for any help with this

W1dge

 

[pbaldy]

This is off the top of my head, but try:

SELECT JobID, Min(Start_Time) AS StartTime, Max(End_Time) AS EndTime
FROM TableName
GROUP BY JobID

 

[w1dge]

Thanks Paul

Will give it a try

W1dge

 

[w1dge]

Paul

Tried what you suggested and it works a treat

Thanks once again

W1dge