Simple Table with Time Window

[BCNAV]

I have a simple query:

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_NOBILL_001A_0009 AS 
   SELECT t1.billable_id, 
          t1.fdaas_flight_id AS fdaas_flight_id_old, 
          t2.fdaas_flight_id AS fdaas_flight_id_new, 
          t1.flightdate, 
          t1.client_id, 
          t1.ac_type, 
          t1.category, 
          t1.powplant, 
          t1.ac_ident, 
          t1.mtow, 
          t1.depart, 
          t1.dest, 
          t1.terminal, 
          t1.enroute, 
          t1.FltDate, 
          t1.gmtdate, 
          t1.UTCDate, 
          t1.FY, 
          t1.gmttime, 
          t1.chargetype_code, 
          t1.ktok, 
          t1.datalink, 
          t1.billdist, 
          t1.cdn_dist, 
          t1.flight_id, 
          t1.billing_flight_id, 
          t1.daily_grouping, 
          t1.status_code, 
          t1.file_name, 
          t1.data_sourc, 
          t1.fnobill, 
          t1.military, 
          t1.Status_Reason_Code, 
          t1.Nobill_Reason
      FROM EGTASK.QUERY_FOR_NOBILL_001A_0000 t1, EGTASK.QUERY_FOR_MOVE_00B6_CRACKER t2
      WHERE (t1.depart = t2.depart AND t1.dest = t2.dest AND t1.flight_id = t2.flight_id AND t1.client_id = t2.client_id 
           AND t1.flightdate = t2.flightdate)
      ORDER BY t1.flightdate;
QUIT;

However, due to timing issues from the tables t1.flightdate and t2.flightdate may be off by a few minutes. So while it is preferable that t1.flightdate = t2.flightdate is true in the where clause, it is also ok that they are within 10 minutes of one another as long as all the other conditions are met. They are datetime variables. What is the best way to go about this? Maybe a full join instead of the "inner" above?  I'm not sure. Thanks.

[Reeza]

t1.flightdate between t2.flightdate-10*60 and t2.flightdate+10*60

Make the join on the time interval of interest?

[ballardw · Accepted solution]

abs(intck('minute', t1.flightdate,t2.flightdate)) le 10

Should do the trick if you don't care which way the time difference is