sqloracle-databaserows

How to make 1 rows from 2 rows which are entry and exit dates


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.


Solution

  • 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.