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

On 4/25/2017 2:26 PM, Steinmetz, Paul wrote:

Still fine tuning this SQL.
Need to add a break on the date, get rid of the comma for the time duration field, HHMMSS, and sum the time duration field, HHMMSS.
I'm not sure this is possible, since these are all substr.

Any thoughts from the group.

SELECT substr(FLASTR,69,8) as "Date" , substr(FLASTR,31,35) as
"Start of Job" , substr(FLASTR,78,11) as " Time" ,
substr(FLAend,58,29) as "End of Job" , (time(substr(flaend,79,8)) -
time(substr(flastr,81,8))) as " HHM MSS " ,
substr(flaend,89,5) as "Total CPU" FROM qgpl.flastr JOIN
qgpl.flaend on substr(FLASTR,31,26) = substr(flaend,31,26) WHERE
substr(FLASTR,38,3) = 'FLA' and substr(FLASTR,47,4) = 'QPAD' ORDER
BY substr(FLASTR,69,8)

"Date" "Start of Job" " Time" "End of Job" " HHM MSS" "Total CPU"
04/01/17 793376/FLACWILS/QPADEV003Z started at 08:56:27 ended on 04/01/17 at 17:30:14 83,347 6.299
04/01/17 793377/FLAAKHAL/QPADEV0040 started at 08:56:39 ended on 04/01/17 at 17:30:14 83,335 4.402
04/01/17 793387/FLAGBRIG/QPADEV004F started at 08:59:00 ended on 04/01/17 at 17:30:07 83,107 7.284
04/02/17 802259/FLAGBRIG/QPADEV0026 started at 08:55:06 ended on 04/02/17 at 17:30:36 83,530 5.250
04/02/17 802270/FLAWCOLL/QPADEV0028 started at 08:57:24 ended on 04/02/17 at 17:30:40 83,316 3.347
04/02/17 802289/FLAHDOUG/QPADEV0027 started at 09:00:09 ended on 04/02/17 at 17:30:42 83,033 3.832

My first thought is that it's going to be UGLY.

First, the commas. I think those commas are put in by the tool you're
using. STRSQL? I'm using ACS and have no commas.

Second, summing up the time duration fields.
Ew.
Those 'duration' fields are in fact a numeric representation of hours,
minutes and seconds. Which are icky to try to accumulate when they're
piled into one field. And then you have to do mod 60 and mod 24 math. Ew.

Instead of doing the difference as time() - time(), consider
timestampdiff(2, char(time() - time()) The '2' means 'return the
difference in seconds'. That'll yield a number that you can accumulate
to get a 'total clock seconds' value. Which is a little less nasty to
convert back to hours, minutes, and seconds.

I kind of cheat with timestamp('0001-01-01 00:00:00') + timestampdiff(2,
char(stamp_to - stamp_fr)) seconds but that gives 1 year, 1 month, 1
day as the 'minimum' value. Only the hours, minutes, and seconds come
out right.

I suppose you could then convert that back to one of those goofy numeric
intervals by subtracting timestamp('0001-01-01 00:00:00') from the above
expression; that would yield a decimal value with days, hours, minutes,
and seconds. Fun with SUBSTR and you could get a readable result out of
the lot.

This gets way, way, way easier if you bust this into several parts. The
first part builds an actual DB2 table with actual date, time, timestamp,
duration, whatever columns. Then the second part analyses that table.