Hourly production data (Progress 4gl)

Question

I am creating a dashboard to show the hourly production data. Need to use the logic to show last 12 hours data.

Example:

Column 1: >= 01:00:00 and < 02:00:00
Column 2: >= 02:00:00 and < 03:00:00

Below is the logic I use:

DEFINE TEMP-TABLE tt_data NO-UNDO
    FIELD ICOUNT        AS INTEGER.
    FIELD cSHPAU        AS CHARACTER
    FIELD cWOBDDDT      AS DATE
    FIELD cWOBTIME      AS INTEGER
    FIELD cWordID       AS CHARACTER.


DEFINE TEMP-TABLE tt_time NO-UNDO
    FIELD A AS INTEGER LABEL "00:00:00 TO 00:59:59"
    FIELD B AS INTEGER LABEL "01:00:00 TO 01:59:59"

DEFINE VARIABLE iTime AS INTEGER NO-UNDO.
DEFINE VARIABLE dDate AS DATE NO-UNDO.

iTime  = TIME - 12 * 3600.
dDate  = NOW.

EMPTY TEMP-TABLE tt_data.

FOR EACH sfcf_au_ship NO-LOCK,
    EACH sfcf_au OF sfcf_au_ship NO-LOCK,
    EACH womf_worder OF sfcf_au_ship 
    WHERE womf_worder.word_production_status = 'B'  
    AND  womf_worder.word_build_time >= iTime 
    AND womf_worder.word_build_date >= dDate NO-LOCK:

    CREATE tt_data.
     ASSIGN
           tt_data.cWordID  = womf_worder.word_id
           tt_data.cWOBDDDT = womf_worder.word_build_date
           tt_data.cWOBTIME = womf_worder.word_build_time
           tt_data.cSHPAU   = sfcf_au.assu_id.

           IF tt_data.cWOBTIME >= ( 00 * 60 * 60 ) AND TIME < ( 01 * 60 * 60 ) THEN DO:
            FIND tt_time NO-LOCK NO-ERROR.
            ASSIGN
                tt_data.cWOBTIME = tt_time.A.
           END.

           IF tt_data.cWOBTIME >= ( 01 * 60 * 60 ) AND TIME < ( 02 * 60 * 60 ) THEN DO:
            FIND tt_time NO-LOCK NO-ERROR.
            ASSIGN
                tt_data.cWOBTIME = tt_time.B.
        END.
END.

Mike, I am trying to create this dashboard and getting failed. Above is the logic I use. — Varun Sulabha, Aug 15 '19 at 06:44
General recommendation would be to change using the DATETIME or datatype and the INTERVAL function. That makes it waaaaayy easier to deal with date and time when midnight comes into play. — Mike Fechner, Aug 15 '19 at 06:46
We cannot run your code because we do not have that database so it isn't like we can use trial and error to figure out what you need. You are not complaining about any particular error and you seem to have figured out 0 to 1 and 1 to 2 so it seems like perhaps you are looking for a more generic approach than hard coding each bracket? — Tom Bascom, Aug 15 '19 at 11:03
Please read this guide on how to ask a good question, and then edit your post. https://stackoverflow.com/help/how-to-ask — jdpjamesp, Aug 15 '19 at 11:37

Tom Bascom · Accepted Answer · 2019-08-15T13:36:55.387

Assuming that your FOR EACH loop is already selecting the correct range of records you can get the hour of the build time a bit more easily:

hh = truncate( word_build_time / 3600, 0 ).

Then just create your temp-table records using that hour as the key. There should only be 12 unique values if the FOR EACH is correct.

So add a new field to the TT called tt_data.whichHour and you will be able to easily group the records by hour. The existing WobDDDT and WobTime fields can be used to properly sort the data.

Side note: the FOR EACH is probably not selecting the proper records. Discrete DATE and TIME fields are tricky when the time period crosses midnight. Your example uses the current date and time (minus 12 hours) so if you run it at 6am you will miss yesterday's data from 6pm to midnight.

To do that properly you need to write a much more complex WHERE clause that covers both periods with an OR. Something along these lines:

FOR EACH womf_worder NO-LOCK
   WHERE womf_worder.word_production_status = 'B'  
     AND (
           ( word_build_date = date1 AND word_build_time >= time1 and word_build_time < time2 )
          OR
           ( word_build_date = date2 AND word_build_time >= time3 and word_build_time < time4 )
         )

To do that you will need to set up at least date1 & date2 and 4 time values.

It is actually probably easier to understand if you simply select the date(s) needed and filter the proper times with an IF statement in the body of the code.

You would be far better off if the table had a DATETIME field. Discrete DATE and TIME fields are something that legacy applications used waaaaaay back in the dark ages before the DATETIME datatype was supported.

I found out an alternate way. Thanks everyone for your time. — Varun Sulabha, Sep 30 '19 at 12:20

Hourly production data (Progress 4gl)

1 Answers1