Hi,
the solution is working fine with the above dataset.
currently 1 entry will be created for each in/out to/from the car park. Is there any way i can count it with the following dataset? Thanks in advance for the help.
original dataset:
| car | in time | out time |
| car1 | 2022-11-22 08:10:00 | |
| car1 | | 2022-11-22 08:40:00 |
| car2 | 2022-11-22 08:20:00 | |
| car2 | | 2022-11-22 09:30:00 |
| car3 | 2022-11-22 09:10:00 | |
| car3 | | 2022-11-22 10:30:00 |
| car4 | 2022-11-22 10:10:00 | |
| car4 | | 2022-11-22 11:30:00 |
| car5 | 2022-11-22 11:00:00 | |
| car5 | | 2022-11-22 13:00:00 |
| car1 | 2022-11-22 10:40:00 | |
| car1 | | 2022-11-22 13:40:00 |
| car2 | 2022-11-22 11:30:00 | |
| car2 | | 2022-11-22 12:30:00 |
| car3 | 2022-11-22 12:30:00 | |
after find the matching paired:
| car | in time | out time | new column (find the matching in-time pair) |
| car1 | 2022-11-22 08:10:00 | | |
| car1 | | 2022-11-22 08:40:00 | 2022-11-22 08:10:00 |
| car2 | 2022-11-22 08:20:00 | | |
| car2 | | 2022-11-22 09:30:00 | 2022-11-22 08:20:00 |
| car3 | 2022-11-22 09:10:00 | | |
| car3 | | 2022-11-22 10:30:00 | 2022-11-22 09:10:00 |
| car4 | 2022-11-22 10:10:00 | | |
| car4 | | 2022-11-22 11:30:00 | 2022-11-22 10:10:00 |
| car5 | 2022-11-22 11:00:00 | | |
| car5 | | 2022-11-22 13:00:00 | 2022-11-22 11:00:00 |
| car1 | 2022-11-22 10:40:00 | | |
| car1 | | 2022-11-22 13:40:00 | 2022-11-22 10:40:00 |
| car2 | 2022-11-22 11:30:00 | | |
| car2 | | 2022-11-22 12:30:00 | 2022-11-22 11:30:00 |
| car3 | 2022-11-22 12:30:00 | | |
expected result:
| | 2022-11-22 08:00-09:00hr | 2022-11-22 09:00-10:00hr | 2022-11-22 10:00-11:00hr | 2022-11-22 11:00-12:00hr | 2022-11-22 12:00-12:59hr | 2022-11-22 13:00-14:00hr |
| number of cars in the carpark | 1 | 1 | 2 | 3 | 3 | 1 |
