Match Report DateTime to DateTime on Site

[Bobbins]

Good Morning All,

Using Valet parking as the example, I am trying to find out when the valet issued a ticket so that it can be matched to the right period of time that the valet was in the parking lot.

 I have two files (both excel files), first is the ticket file. (This normally has 1000+ lines)
File 1

Person	DateTime for Ticket (yyyy-mm-dd hh:mm)	Unique Code
a,smith	2022-05-04 12:35	A12
b,jones	2022-05-05 15:45	C54
a,smith	2022-05-05 15:59	AA59
c,charlie	2022-05-05 21:21	Z65
a,smith	2022-05-06 01:32	G56

File 2 9(This normally has 20000+ lines)

This file shows the entry and exit time of the person from the site. Often many multiples of entry and exit for people, including across a date shift)

Person	Entry (yyyy-mm-dd hh:mm)	Exit (yyyy-mm-dd hh:mm)
a,smith	2022-05-04 06:35	2022-05-04 15:35
b,jones	2022-05-05 14:44	2022-05-05 16:13
a,smith	2022-05-05 12:13	2022-05-05 16:50
b,jones	2022-05-05 18:12	2022-05-05 23:58
c,charlie	2022-05-05 19:21	2022-05-05 22:13
c,charlie	2022-05-05 23:59	2022-05-06 06:15
a,smith	2022-05-05 23:59	2022-06-06 12:15

This should eventually give us:

Person	Entry (yyyy-mm-dd hh:mm)	Exit (yyyy-mm-dd hh:mm)	DateTime for Ticket (yyyy-mm-dd hh:mm)	Unique Code
a,smith	2022-05-04 06:35	2022-05-04 15:35	2022-05-04 12:35	A12
b,jones	2022-05-05 14:44	2022-05-05 16:13	2022-05-05 14:44	C54
a,smith	2022-05-05 12:13	2022-05-05 16:50	2022-05-05 15:59	AA59
b,jones	2022-05-05 18:12	2022-05-05 23:58
c,charlie	2022-05-05 19:21	2022-05-05 22:13	2022-05-05 21:21	Z65
c,charlie	2022-05-05 23:59	2022-05-06 06:15
a,smith	2022-05-05 23:59	2022-06-06 12:15	2022-05-06 01:32	G56

I have tried doing summary's but i come up blank. How is this best solved?

Thank you

Bob

[grazitti_sapna · Accepted solution]

Hi @Bobbins , You can accomplish your output by using join tool components. Here is my solution. Please accept it if this is what you were looking for.

Thanks!!

[DataNath · Accepted solution]

Here's how I'd go about this - just need to join, before filtering out those DateTimes that don't fall within the Entry/Exit range. Then we can rejoin those to the original before unioning any records without a match.

Note: In my filter I've used > and < explicitly, but if you want to include the Entry and Exit times themselves, obviously just use => and <= respectively.

[Bobbins]

Thank you Both, whilst both are acceptable, i ended up using @DataNath version for my needs.