I receive the following result set from TableA (In Time)
7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM

Then we receive the following result from TableB (Out Time)
7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM
[NULL]
[NULL]

We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.

I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.


and out_time = (select min (out_time)
FROM tableB WHERE
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id

GROUP BY tableB.record#, tableB.loc_id )

It seems I need to focus on the minimum datediff for each record line but can't figure that part out. Any suggestions or tips is greatly appreciated.