Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Records Prior to the Prior Hour

Status
Not open for further replies.

eric333

IS-IT--Management
Nov 3, 2007
76
US
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))))

 
({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
 
Worked perfectly. Thank you very much.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top