Re: Need to find job start, job end, calculate total job time for a group of users, TST*, for a selected date range

select x.message_timestamp as start_time,
y.message_timestamp as end_time,
y.message_timestamp - x.message_timestamp as job_duration,
x.from_job, y.from_job
from table(qsys2.history_log_info(current date - 2 days,
timestamp((current date - 1 days)) - 1 seconds)) x
full outer join table(qsys2.history_log_info(current date - 2 days,
timestamp((current date - 1 days)) - 1 seconds)) y
using (from_job)
where x.message_id = 'CPF1124' and y.message_id = 'CPF1164';

There are rare occasions where the end time is before the start type, by
milliseconds.

I'll leave it up to you to convert this decimal
y.message_timestamp - x.message_timestamp as job_duration,
to a time or timestamp field.


Rob Berendt