0010_list_jobs_long_last

# "WHERE JobId > 0" is a dummy where clause,
#                   to make it easier to extend the filter
#                   (all filters starts with "AND ...")

SELECT DISTINCT Job.JobId, Job.Job, Job.Name, Job.PurgedFiles, Job.Type, Job.Level,
Job.ClientId, Client.Name as Client, Job.JobStatus, Job.SchedTime, Job.StartTime,
Job.EndTime, Job.RealEndTime,
CASE
    WHEN Job.endtime IS NOT NULL AND Job.endtime >= Job.starttime THEN Job.endtime - Job.starttime
    ELSE CURRENT_TIMESTAMP(0) - Job.starttime
    END as Duration,
Job.JobTDate, Job.VolSessionId, Job.VolSessionTime,
Job.JobFiles, Job.JobBytes, Job.JobErrors, Job.JobMissingFiles, Job.PoolId,
Pool.Name as PoolName, Job.PriorJobId, Job.FileSetId, FileSet.FileSet
FROM Job
LEFT JOIN Client ON Client.ClientId=Job.ClientId
LEFT JOIN Pool ON Pool.PoolId=Job.PoolId
LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId
LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId
LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId
INNER JOIN (
   SELECT MAX(Job.JobId) as MaxJobId
   FROM Job
   LEFT JOIN Client ON Client.ClientId=Job.ClientId
   LEFT JOIN Pool ON Pool.PoolId=Job.PoolId
   LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId
   LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId
   LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId
   WHERE Job.JobId > 0
   %s
   GROUP BY Job.Name
) LastJob
ON Job.JobId = LastJob.MaxJobId
ORDER BY StartTime%s;