Select Records Prior to the Prior Hour

[eric333]

Good evening,

I have a report that selects data from two tables that are joined by a common field in both tables - the event number. Table 1 has one instance of each event number - Table 2 can have multiple instances of each event number.

I need to be able to select records that were entered (based on the timestamp field) in Table 2 during the prior hour, but entered in Table 1 any time prior to the prior hour but not within the prior hour.

I can use the following formula to include records from Table 2, but I'm not sure how to make it work with Table 1. Thanks in advance for any assistance.

The report runs every hour and looks for all new events entered during the prior hour, using the following selection formula:

({event_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))))

 

[lbass]

({table2.event_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))) and
({table1.event_entered_datetime} < dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))

-LB

 

[eric333]

Worked perfectly. Thank you very much.