Let's say I have a table like this:

I want to end up with 3 rows like these:

I guess I can do it with a Lag function. But what is the correct way of doing this? Thanks in advance.

CodePudding user response：

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.

CodePudding user response：

You will have to calculate the next entrance using a LEAD function, so you can join your table to itself.

One way to do it is to create two CTEs, one containing all entrances and one containing all exits and join these by PERSON and assuring that the exit it picks is later than the entrance, and earlier or equal to the next entrance (or in case there is no next entrance, include it as well, that is what the COALESCE is doing in this example:

db<>fiddle here

WITH YOUR_TABLE AS (
SELECT 'one' AS PERSON, TO_DATE('09.08.2022', 'DD.MM.YYYY') AS ENTRANCE, NULL AS EXIT FROM DUAL
UNION SELECT 'one', NULL, TO_DATE('10.08.2022', 'DD.MM.YYYY') FROM DUAL
UNION SELECT 'one', TO_DATE('10.08.2022', 'DD.MM.YYYY'), NULL  FROM DUAL
UNION SELECT 'one', NULL, TO_DATE('13.08.2022', 'DD.MM.YYYY') FROM DUAL
UNION SELECT 'two', TO_DATE('08.08.2022', 'DD.MM.YYYY'), NULL  FROM DUAL
UNION SELECT 'two', NULL, TO_DATE('12.08.2022', 'DD.MM.YYYY') FROM DUAL
), 

ENTRANCES AS (
  SELECT 
    PERSON,  
    ENTRANCE, 
    LEAD(ENTRANCE) OVER (PARTITION BY PERSON ORDER BY ENTRANCE) NEXT_ENTRANCE
  FROM YOUR_TABLE
  WHERE ENTRANCE IS NOT NULL),

EXITS AS (
  SELECT PERSON, EXIT FROM YOUR_TABLE
  WHERE EXIT IS NOT NULL)
  
SELECT ENTRANCES.PERSON, ENTRANCES.ENTRANCE, EXITS.EXIT
FROM ENTRANCES
JOIN EXITS
ON ENTRANCES.PERSON = EXITS.PERSON
AND EXITS.EXIT > ENTRANCES.ENTRANCE 
AND EXITS.EXIT <= COALESCE(ENTRANCES.NEXT_ENTRANCE, TO_DATE('99991231', 'YYYYMMDD'))
ORDER BY 1, 2;

disclaimer: This only works if persons can't enter and exit on the same date.