Joining on closest date

Question

Hi y'all, I wanted to see if there was a way to join on the closest dates depending on a time range. I have two tables that I'm trying to do a left join on.

For example, if on the left table date it says: 10/18/2022 17:22, and I designate the time range to be plus or minus two hours, I would like to do a join on dates like 10/18/2022 18:34, 10/18/2022 16:78, but not 10/18/2022 15:00 or 10/18/2022 20:22 since that is outside the two hour window.

The left table has this data:

DATETIME_DValue110/18/2022 17:220.0110/18/2022 20:410.020910/19/2022 0:360.008210/19/2022 2:320.043610/19/2022 4:370.012810/19/2022 6:330.010210/19/2022 7:000.010210/19/2022 10:000.006610/19/2022 10:040.008710/19/2022 22:000.019510/20/2022 2:000.021410/20/2022 6:270.010310/20/2022 10:000.003210/20/2022 14:000.007110/20/2022 21:070.009810/21/2022 10:000.008710/21/2022 14:000.00410/21/2022 19:000.005210/21/2022 22:000.003410/22/2022 2:000.003110/22/2022 6:510.003210/22/2022 14:000.003610/22/2022 19:000.003910/22/2022 22:000.005210/23/2022 2:000.008110/23/2022 7:150.003610/23/2022 10:000.004210/23/2022 14:000.004510/23/2022 19:000.007810/23/2022 22:000.009610/24/2022 2:000.008810/24/2022 7:090.005410/24/2022 10:000.003610/24/2022 14:000.00510/24/2022 19:000.006610/24/2022 22:000.016710/25/2022 2:000.013810/25/2022 7:000.023310/25/2022 12:390.014410/25/2022 18:060.012910/25/2022 21:080.019210/25/2022 22:000.00710/26/2022 2:000.008610/26/2022 7:000.01610/26/2022 10:000.021910/26/2022 13:050.014910/26/2022 14:220.013210/26/2022 17:400.007110/26/2022 21:020.007210/26/2022 22:000.005310/27/2022 0:550.008110/27/2022 2:000.007910/27/2022 5:230.008510/27/2022 7:000.007810/27/2022 11:020.013610/27/2022 14:060.017510/27/2022 21:210.004810/27/2022 22:000.004410/28/2022 2:000.005610/28/2022 7:000.022310/28/2022 13:180.024210/28/2022 16:110.014910/28/2022 19:160.007210/28/2022 21:080.003910/28/2022 22:000.00710/29/2022 2:000.005110/29/2022 5:320.004710/29/2022 7:000.003910/29/2022 10:000.005310/29/2022 14:000.003710/29/2022 19:170.003910/30/2022 0:160.003610/30/2022 3:200.003310/30/2022 7:120.00510/30/2022 10:000.006110/30/2022 19:000.004710/31/2022 0:120.005910/31/2022 2:000.004410/31/2022 7:000.011110/31/2022 10:000.007910/31/2022 14:000.004410/31/2022 19:000.007711/1/2022 2:000.007111/1/2022 7:000.008711/1/2022 10:000.006611/1/2022 19:000.006911/1/2022 22:000.003911/2/2022 2:000.003611/2/2022 7:000.00311/2/2022 10:000.003311/2/2022 14:000.003611/2/2022 19:000.0065

The right table has this data:

DATETIME_DValue210/18/20221.39540410/18/2022 1:001.34162510/18/2022 2:001.34447310/18/2022 3:001.34678110/18/2022 4:001.36531810/18/2022 5:001.3956110/18/2022 6:001.37286910/18/2022 7:001.37709410/18/2022 8:001.3710/18/2022 9:001.32175210/18/2022 10:001.37681910/18/2022 11:001.33365710/18/2022 12:001.38204910/18/2022 13:001.34953910/18/2022 14:001.34896910/18/2022 15:001.32773810/18/2022 16:001.36317310/18/2022 17:001.33466310/18/2022 18:001.33260510/18/2022 19:001.35370510/18/2022 20:001.36113410/18/2022 21:001.32140910/18/2022 22:001.31118210/18/2022 23:001.36163510/19/20221.36612210/19/2022 1:001.37301310/19/2022 2:001.37664210/19/2022 3:001.36831810/19/2022 4:001.33240710/19/2022 7:001.35210910/19/2022 8:001.3482110/19/2022 9:001.33857710/19/2022 10:001.34668910/19/2022 11:001.34654910/19/2022 12:001.34257810/19/2022 13:001.33884110/19/2022 14:001.32227410/19/2022 19:001.33954110/19/2022 20:001.38123810/19/2022 21:001.3510/19/2022 22:001.41012910/19/2022 23:001.41453310/20/20221.39549510/20/2022 1:001.39968610/20/2022 2:001.36494410/20/2022 3:001.39287710/20/2022 4:001.39543810/20/2022 5:001.36860510/20/2022 6:001.35346610/20/2022 7:001.38388710/20/2022 8:001.33947110/20/2022 9:001.35538410/20/2022 10:001.36385410/20/2022 11:001.38072910/20/2022 12:001.3510/20/2022 13:001.40077710/20/2022 14:001.38393310/20/2022 20:001.34872510/21/2022 11:001.34678710/21/2022 12:001.36454210/21/2022 13:001.32027410/21/2022 14:001.368310/21/2022 15:001.36684410/21/2022 16:001.35712310/21/2022 17:001.38655310/21/2022 18:001.36708810/21/2022 19:001.37591710/21/2022 20:001.35021110/21/2022 21:001.3442310/21/2022 22:001.36518910/21/2022 23:001.371710/22/20221.37883210/22/2022 1:001.37311810/22/2022 2:001.3747110/22/2022 3:001.35549310/22/2022 4:001.37606310/22/2022 5:001.34127810/22/2022 6:001.37547910/22/2022 9:001.35191410/22/2022 10:001.37647710/22/2022 11:001.3599910/22/2022 15:001.37363110/22/2022 16:001.37304310/22/2022 17:001.3705410/22/2022 18:001.3710/22/2022 19:001.38252210/22/2022 20:001.36500110/22/2022 21:001.38812710/22/2022 22:001.39719610/22/2022 23:001.37235510/23/2022 2:001.36064110/23/2022 3:001.40305610/23/2022 4:001.37679410/23/2022 5:001.36496410/23/2022 6:001.35427110/23/2022 9:001.36966710/23/2022 10:001.3534510/23/2022 11:001.3656910/23/2022 12:001.35853110/23/2022 16:001.35912610/23/2022 17:001.35585610/23/2022 18:001.36924410/23/2022 19:001.37364210/24/2022 1:001.38398910/24/2022 2:001.3588110/24/2022 3:001.38434410/24/2022 4:001.39537110/24/2022 5:001.3458910/24/2022 6:001.31901710/24/2022 7:001.35833910/24/2022 8:001.35006510/24/2022 9:001.32748610/24/2022 10:001.3110/24/2022 11:001.3437710/24/2022 12:001.34106710/24/2022 13:001.31551910/24/2022 14:001.38016810/24/2022 15:001.39666510/24/2022 16:001.39819210/24/2022 17:001.3710/24/2022 18:001.38505810/24/2022 19:001.36655610/24/2022 22:001.3880410/24/2022 23:001.3326110/25/20221.36481610/25/2022 1:001.37692910/25/2022 2:001.35029610/25/2022 3:001.35713810/25/2022 4:001.30448810/25/2022 5:001.29799210/25/2022 6:001.34268810/25/2022 7:001.34182110/25/2022 8:001.3310/25/2022 9:001.3310/25/2022 12:001.33855710/25/2022 13:001.38166510/25/2022 14:001.3910/25/2022 16:001.3910/25/2022 17:001.35537810/25/2022 18:001.35391810/25/2022 19:001.33465110/25/2022 20:001.33366110/25/2022 21:001.32181510/25/2022 22:001.32722410/25/2022 23:001.35613710/26/20221.37736410/26/2022 1:001.33676210/26/2022 2:001.35245610/26/2022 3:001.31602510/26/2022 4:001.32963710/26/2022 5:001.30833810/26/2022 6:001.33126310/26/2022 7:001.36698810/26/2022 8:001.362910/26/2022 9:001.36423810/26/2022 10:001.36091810/26/2022 11:001.34788610/26/2022 12:001.3705510/26/2022 13:001.3747910/26/2022 14:001.38390710/26/2022 15:001.36444910/26/2022 16:001.36852910/26/2022 17:001.36387610/26/2022 18:001.3752810/26/2022 20:001.3910/26/2022 21:001.37491710/26/2022 22:001.3668610/26/2022 23:001.36020110/27/20221.3720110/27/2022 1:001.3576610/27/2022 2:001.37038210/27/2022 3:001.36563810/27/2022 4:001.36724310/27/2022 5:001.3910/27/2022 6:001.36498110/27/2022 7:001.36485210/27/2022 8:001.36377410/27/2022 9:001.36879810/27/2022 10:001.38093210/27/2022 11:001.38565310/27/2022 12:001.36708810/27/2022 13:001.36015310/27/2022 14:001.40855410/27/2022 15:001.36338110/27/2022 16:001.410/27/2022 17:001.34970310/27/2022 18:001.35298910/27/2022 19:001.34901710/27/2022 20:001.39587410/27/2022 21:001.37700410/27/2022 22:001.38290510/27/2022 23:001.37249210/28/20221.38333210/28/2022 1:001.35643710/28/2022 2:001.37478810/28/2022 3:001.37955410/28/2022 4:001.37360910/28/2022 9:001.35469210/28/2022 13:001.35259910/28/2022 14:001.3510/28/2022 15:001.3987910/28/2022 16:001.34263710/28/2022 17:001.3456510/28/2022 18:001.38078210/28/2022 19:001.38364910/28/2022 20:001.38282810/28/2022 21:001.35060210/28/2022 22:001.37999110/28/2022 23:001.40488910/29/20221.3910/29/2022 1:001.35503810/29/2022 2:001.39899110/29/2022 3:001.38103410/29/2022 4:001.348310/29/2022 5:001.37183910/29/2022 6:001.31653510/29/2022 7:001.3229310/29/2022 8:001.38444910/29/2022 9:001.38580410/29/2022 10:001.37823710/29/2022 11:001.37345510/29/2022 12:001.34832310/29/2022 13:001.33935210/29/2022 14:001.36596210/29/2022 16:001.38040410/29/2022 17:001.33529910/29/2022 18:001.36984410/29/2022 19:001.36183610/29/2022 20:001.34810410/29/2022 21:001.34160710/29/2022 22:001.33692810/29/2022 23:001.36241110/30/20221.36544310/30/2022 1:001.33925410/30/2022 2:001.32819710/30/2022 3:001.30066810/30/2022 4:001.37673910/30/2022 7:001.36549710/30/2022 8:001.3610/30/2022 9:001.35159410/30/2022 10:001.38334310/30/2022 11:001.35062110/30/2022 12:001.36740410/30/2022 13:001.3386510/30/2022 14:001.36638810/30/2022 15:001.34883810/30/2022 16:001.36556310/30/2022 17:001.33179410/30/2022 18:001.32508710/30/2022 19:001.35872910/30/2022 20:001.34879610/30/2022 21:001.35343910/30/2022 22:001.33489210/30/2022 23:001.32555710/31/20221.31219710/31/2022 1:001.32200910/31/2022 2:001.31675310/31/2022 3:001.32383510/31/2022 4:001.34892210/31/2022 5:001.3655110/31/2022 6:001.30673810/31/2022 7:001.33291410/31/2022 8:001.31579610/31/2022 9:001.35851110/31/2022 10:001.34455410/31/2022 11:001.310/31/2022 12:001.35513210/31/2022 13:001.35331910/31/2022 14:001.30507510/31/2022 15:001.32380910/31/2022 18:001.37763410/31/2022 19:001.3687310/31/2022 20:001.38714510/31/2022 21:001.3716411/1/2022 1:001.34558511/1/2022 2:001.35323711/1/2022 3:001.38359611/1/2022 4:001.35073311/1/2022 5:001.33742211/1/2022 6:001.33422211/1/2022 7:001.36701111/1/2022 8:001.3589511/1/2022 9:001.36820911/1/2022 10:001.35928711/1/2022 11:001.34846711/1/2022 12:001.36888511/1/2022 13:001.38508711/1/2022 18:001.37384311/1/2022 19:001.38323411/1/2022 20:001.34553311/1/2022 21:001.35221811/1/2022 22:001.31768211/1/2022 23:001.35345811/2/20221.35705411/2/2022 2:001.34643611/2/2022 3:001.33230211/2/2022 4:001.36555811/2/2022 5:001.33768611/2/2022 6:001.32410611/2/2022 9:001.31479911/2/2022 10:001.24961711/2/2022 11:001.25200911/2/2022 12:001.35213511/2/2022 13:001.32617811/2/2022 14:001.35206411/2/2022 15:001.35528111/2/2022 16:001.32142211/2/2022 17:001.33806811/2/2022 18:001.363611/2/2022 19:001.365102

Verakso · Accepted Answer

I have no idea if this comes close to what you are looking for, but I gave it a try.

The thing is, you really can't do a left or right outer join, well, that was until @NeilR made The Advanced Join Macro

I have used that to join on your two sets of data, where I first made a Lowe and an Upper datetime for the +- 2 hours inteval

You can play with the attached workflow

/Verakso (ツ)

DanM · Accepted Answer

Here's a suggestion;

Since your sample data has the same format, add a Formula tool after each Input tool and create a new field. Name the field it whatever you want, but maybe "Input". In one Formula tool add in the expression "Input 1" and leave as a string and the other Formula tool do the same but name it "Input 2".

Then use the Union tool to put the data together. After the Union tool use a Sort tool on your DATETIME_D field.

You can then use a Filter tool to do a less than this date and more than this date expression. Make sure your date field is listed as Date or DateTime in the meta data.

That will give you the data for bother data sets within the time range you want. You will also be able to see what data came from what data set with the new field you created.

Hope that helps.