Let's say I have a table like this:
Person | Entrance | Exit |
---|---|---|
One | 09.08.2022 | |
One | 10.08.2022 | |
One | 10.08.2022 | |
One | 13.08.2022 | |
Two | 08.08.2022 | |
Two | 12.08.2022 |
I want to end up with 3 rows like these:
Person | Entrance | Exit |
---|---|---|
One | 09.08.2022 | 10.08.2022 |
One | 10.08.2022 | 13.08.2022 |
Two | 08.08.2022 | 12.08.2022 |
I guess I can do it with a Lag function. But what is the correct way of doing this? Thanks in advance.
Supposing that each entrance date has an exit date, you may try the following:
Select D.Person,MAX(Entrance) Entrance, MAX(Exit) Exit
From
(
Select table_name.*,
MOD(ROW_NUMBER() Over (Partition By Person Order By Entrance, Exit),
COUNT(*) Over (Partition By Person)/2) grp
From table_name
) D
Group By D.Person, D.grp
See a demo.