CODEWITHSUNDEEP
openedgeprogress-4glprogress-db
Thiru
Thiru

Reputation: 251

How to calculate yesterday created records with today?

Hi I would like to know how to calculated records which was created yesterday at some time and calculate with today produced records. please look my code.

   FOR EACH womf_worder OF sfcf_au_ship WHERE womf_worder.word_production_status = "B"
                                 AND womf_worder.word_build_date = DATE(TODAY)    
                                 AND womf_worder.word_build_time GE  tt_shift.shft_start_hour
                                 AND womf_worder.word_build_time LE tt_shift.shft_stop_hour NO-LOCK: 

     IF AVAILABLE womf_worder THEN DO: 
         i = i + 1.         
     END.

tt_shift.shft_start_hour = 06:00:00 and stop_hour = 23:50:00

Here my question is how can calculate records which will be produced by tomorrow with yesterday records. How can i use DATETIME for this?

Upvotes: 0

Views: 150

Answers (2)

user10979065
user10979065

Reputation: 26

for each womf_worder of sfcf_au_ship where 
         womf_worder.word_production_status EQ "B"                      and 
         womf_worder.word_build_date        EQ today - 1                and 
         womf_worder.word_build_time        GE tt_shift.shft_start_hour and
         womf_worder.word_build_time        LE tt_shift.shft_stop_hour  
         no-lock: 

    assign i = i + 1.
end.

Notes:

  1. Function "today" returns a datetype value, dont need casting date();
  2. Use expression "today - 1";
  3. Don't need "if avail" condition for "for each" blocks, if AVM finds any record, it aways avail;

Upvotes: 1

Jensd
Jensd

Reputation: 8011

DATETIME would be very usable if any field in the database is DATETIME. A quick lock at your example makes me think you have separate date and time fields. I'm unsure what the TIME field is. Perhaps an integer or a string?

You don't need the availability check in a FOR-loop. It's for FINDS and possibly JOINS where a record might not be available in the loop. For a basic FOR EACH like that only available records will be processed.

If the time is indeed an integer or a string your code could very well work with a minor fix for the availability check. 

   FOR EACH womf_worder OF sfcf_au_ship WHERE womf_worder.word_production_status = "B"
                                 AND womf_worder.word_build_date = DATE(TODAY)    
                                 AND womf_worder.word_build_time GE tt_shift.shft_start_hour
                                 AND womf_worder.word_build_time LE tt_shift.shft_stop_hour NO-LOCK: 

         i = i + 1.         
     END.

Upvotes: 4

Related Questions