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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

You could also use below solution which takes advantage of LEAD analytic function with its option IGNORE NULLS.

select Person, evt_date as Entrance, exit_date as Exit
from (
    select Person, evt_date, evt
        , lead(case when evt = 2 then evt_date else null end, 1) 
            ignore nulls over(
                partition by Person 
                    order by evt_date, evt desc
            ) as exit_date
    from (
        select Person
            , nvl(Entrance, Exit) as evt_date
            , case when Entrance is not null then 1 else 2 end evt
        from YourSampleData
    ) t1
)t2
where evt = 1 /*filter out all the rows initially containing exit dates data*/
order by Person, Entrance
;

demo

Answer 4

as per your requerment you can get output with LEAD() Function

With T1 As
(Select Person,Entrance,LEAD(Exitt,1,'') Over (Partition by Person Order By 
Person) Exitt From Person2)
Select * From T1 Where Entrance <> '' And Exitt <> ''

Answer 5

This works better if you have missing exit date

select d.* from (
    select d.person_id, entry_dat, lead(exit_dat,1) over(partition by person_id, grp_entry order by nvl(entry_dat, exit_dat)) as exit_dat
    from (
        select d.*, 
            count(entry_dat) over(partition by person_id order by nvl(entry_dat, exit_dat)) as grp_entry 
        from data d
    ) d
) d
where entry_dat is not null
;

But still not perfect if you have missing entry date: the orphan exit will not be in the output.

Answer 6

REEDITED ANSWER
Completely changed answer after discution about missing exits or entrances as well as about entrance and exit on the same day. Tryed to cover it like below.
With sample data containing missing exits and entrances and having some same day entrance and exit:

ID	ENT	EXT
1	09-AUG-22
1		10-AUG-22
1	10-AUG-22
1	11-AUG-22
1		13-AUG-22
2	09-AUG-22
2		09-AUG-22
2	09-AUG-22
2		11-AUG-22
2		12-AUG-22
2	12-AUG-22
2		12-AUG-22
3	09-AUG-22
3		11-AUG-22
3		12-AUG-22

Here is the code:

SELECT DISTINCT 
        ID, ENTRANCE, EXIT,  
                CASE  WHEN  ENT_ORD = 1 AND EXT_ORD > 1  And 
                            MAX(EXT_ORD) OVER(PARTITION BY ID, ENTRANCE ORDER BY ID, ENTRANCE) > 1 And 
                            MAX(ENT_ORD) OVER(PARTITION BY ID, EXIT, ENT_ORD ORDER BY ID, EXIT) =  COUNT(EXT_ORD) OVER(PARTITION BY ID, ENTRANCE ORDER BY ID, EXIT) THEN 'Entrance 1 of 2'
                      WHEN  ENT_ORD > 1 AND EXT_ORD = 1  And 
                            MAX(ENT_ORD) OVER(PARTITION BY ID, EXIT ORDER BY ID, EXIT) > 1 And 
                            MAX(EXT_ORD) OVER(PARTITION BY ID, ENTRANCE, EXT_ORD ORDER BY ID, ENTRANCE) =  COUNT(ENT_ORD) OVER(PARTITION BY ID, EXIT ORDER BY ID, ENTRANCE) THEN 'Exit 2 of 2'
                      WHEN ENTRANCE = EXIT THEN 'Entrance and exit on same day'
                END "NOTICE"
FROM
    (
        SELECT  t.ID, t.ENTRANCE, t.EXIT, ENT, EXT,
                Sum(1) OVER(PARTITION BY ID, ENT Order By ID, ORD) "ENT_ORD",
                Sum(1) OVER(PARTITION BY ID, EXT Order By ID, ORD) "EXT_ORD"  
        FROM
            ( SELECT  ORD, ID, ENT, EXT,
                        COALESCE(ENT, 
                                  First_Value(ENT) OVER(Partition By ID Order By ID, ORD asc nulls last ROWS BETWEEN 1 PRECEDING And 1 PRECEDING), 
                                  First_Value(ENT) OVER(Partition By ID Order By ID, ORD asc nulls last ROWS BETWEEN 2 PRECEDING And 2 PRECEDING)
                                ) "ENTRANCE",
                        COALESCE(EXT, 
                                  Last_Value(EXT) OVER(Partition By ID Order By ID, ORD desc nulls first ROWS BETWEEN 1 PRECEDING And 1 PRECEDING), 
                                  Last_Value(EXT) OVER(Partition By ID Order By ID, ORD desc nulls first ROWS BETWEEN 2 PRECEDING And 2 PRECEDING)
                                ) "EXIT"
              FROM    
                        ( SELECT ID, ENT, EXT, Sum(1) OVER(PARTITION BY 1 ORDER BY 1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ORD" FROM tbl )
              ORDER BY ORD ) t
        ORDER BY ID, ENTRANCE
    )
ORDER BY ID, ENTRANCE

... and the result with notices

ID	ENTRANCE	EXIT	NOTICE
1	09-AUG-22	10-AUG-22
1	10-AUG-22	13-AUG-22	Entrance 1 of 2
1	11-AUG-22	13-AUG-22
2	09-AUG-22	09-AUG-22	Entrance and exit on same day
2	09-AUG-22	11-AUG-22
2	09-AUG-22	12-AUG-22	Exit 2 of 2
2	12-AUG-22	12-AUG-22	Entrance and exit on same day
3	09-AUG-22	11-AUG-22
3	09-AUG-22	12-AUG-22	Exit 2 of 2

It covers the case when ID=2 enters on AUG-09 exits the same day and reenters for the second time on the same AUG-09 followed by two exits on AUG-11 and AUG-12. After that he enters and exits on AUG-12 again. Regards...