I would like to calculate how long did it take to run a Batch job. I know I can use HDIFF to calculate the difference between two dates but how do I get difference when working with one one date column?
For Ex: Duration for Batch_ID A123 in hours is 1, in minutes is 64, and in seconds is 3884This message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8 Windows, All Outputs
April 16, 2019, 02:21 PM
BabakNYC
What is the duration for batch id B567? Are you just comparing the first batch id to the last one? If that's the case, you should: 1. Make sure the data is sorted by Batch ID. 2. Use LAST syntax to find the last batch id and flag it so you know what two values have to be subtracted to get the difference.
Ideally, your database would have a START and END date times so you could avoid all this because you'll also have to account for when the clock resets from 23:59 to 0:00 at midnight and if your times are split between two dates, your calculation would have to account for that.
I would suggest a multi-verb for calculating the difference between a batch's earliest and last timestamps. Off the top of my head:
TABLE FILE X
SUM
MIN.Time_Run
MAX.Time_Run
COMPUTE Time_Spent/I10 = HDIFF(MAX.Time_Run, MIN.Time_Run, 'SECOND', Time_Spent);
BY Batch_ID
PRINT
COMPUTE Step_Time/I10 = HDIFF(Time_Run, LAST Time_Run, 'SECOND', Step_Time);
BY Batch_ID
BY Time_Run NOPRINT
END
You probably want to special-case the first row of Step_Time though, as you'll get a huge number otherwise (Batch start time - basedate:'1900/12/31').
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :