WITH arrivals AS (
/* all arrival rows for Uganda (we keep all arrivals so we can pair with
departures) */
SELECT a.*,
/* find the earliest departure after this arrival (NULL if not departed) */
(SELECT MIN(d.departure_date)
FROM departure_table d
WHERE d.passport_no = a.passport_no
AND d.departure_date > a.arrival_date
) AS matched_departure
FROM arrival_table a
WHERE LOWER([Link]) = 'uganda' -- adjust if you store country
codes
),
stays AS (
/* compute overlap of each arrival->departure segment with the year 2024 */
SELECT
passport_no,
passenger_name, -- optional, remove if not present
arrival_date,
matched_departure,
/* the portion of the stay that falls inside 2024 */
TRUNC(GREATEST(arrival_date, DATE '2024-01-01')) AS stay_start_2024,
TRUNC(LEAST(NVL(matched_departure, DATE '2024-12-31'), DATE '2024-12-
31')) AS stay_end_2024
FROM arrivals
)
SELECT
passport_no,
passenger_name,
/* number of arrival events that overlap the year 2024 for this passport */
SUM(CASE
WHEN stay_end_2024 >= stay_start_2024 THEN 1
ELSE 0
END) AS arrivals_overlapping_2024,
/* total days present in India during 2024 (inclusive) */
SUM(CASE
WHEN stay_end_2024 >= stay_start_2024
THEN (stay_end_2024 - stay_start_2024) + 1
ELSE 0
END) AS total_days_in_2024
FROM stays
GROUP BY passport_no, passenger_name
HAVING SUM(CASE
WHEN stay_end_2024 >= stay_start_2024
THEN (stay_end_2024 - stay_start_2024) + 1
ELSE 0
END) > 200
ORDER BY total_days_in_2024 DESC;