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