Overlapping and Non overlappping SQL query

We have below schema:

CREATE TABLE enrollments (
    case_id INT,
    enrollment_id VARCHAR(10),
    open_date DATE,
    closed_date DATE,
    status VARCHAR(10)
);

Let’s assume the records are:

INSERT INTO enrollments (case_id, enrollment_id, open_date, closed_date, status)
VALUES
(1, 'ab', '2024-10-01', '2024-10-05', 'closed'),
(1, 'bc', '2024-10-03', '2024-10-04', 'closed'),
(1, 'bd', '2024-10-05', '2024-10-15', 'closed'),
(1, 'sx', '2024-10-12', '2024-10-19', 'closed'),
(1, 'za', '2024-10-16', '2024-10-21', 'closed'),
(1, 'ca', '2024-10-25', '2024-10-26', 'closed'),
(1, 'dd', '2024-10-25', '2024-10-27', 'closed'),
(1, 'kh', '2024-10-28', NULL, 'open'),
(1, 'kk', '2024-10-28', NULL, 'open');

I want only the records

1. Consider only the cpe’s which are not overlapping with previous enrollments, i.e. the open date of current enrollment should not fall between open and closed date of previous enrollment.

2. If two enrollments are started on same open date consider the long lasting enrollemnt, i.e. latest closed_date.

3. If we have two open enrollments which open dates are not overlapping consider both of them.

4. Validate the current enrollment only with previous valid enrollments which means the record ‘za’ enrollment is not overlapping with previous valid enrollment, then we need to consider this record even though it is overlapping with ‘sx’ record becuase ‘sx’ is an invalid cpe as it overlapped.

So I need this output:

How can I do this?

You need to sign in to view this answers