CODEWITHSUNDEEP
sql-server
Sivabalanarayanan L
Sivabalanarayanan L

Reputation: 104

Finding records in overlapping date range

How to find the records with overlapping date range?

I need to get the list of role_name for which the date ranges are overlapping.

Role Table

Here, the Admin and Warehouse Manager roles are valid and all other roles are overlapping. Also, when the end date is null, the roles added after the StartDate is considered as overlapping.

For example:

create table #Roles
(
id int,
role_name varchar(50),
start_date datetime,
end_date datetime
)


insert into #Roles (id, role_name, start_date, end_date) values ('1','Admin','2020-12-01','2020-12-11');
insert into #Roles (id, role_name, start_date, end_date) values ('2','Admin','2020-12-12',null);
insert into #Roles (id, role_name, start_date, end_date) values ('3','Warehouse manager','2020-12-01','2020-12-09');
insert into #Roles (id, role_name, start_date, end_date) values ('4','Warehouse manager','2020-12-10','2020-12-12');
insert into #Roles (id, role_name, start_date, end_date) values ('5','Buyer','2020-12-01','2020-12-11');
insert into #Roles (id, role_name, start_date, end_date) values ('6','Buyer','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('7','Supervisor','2020-12-01',null);
insert into #Roles (id, role_name, start_date, end_date) values ('8','Supervisor','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('9','Sales Rep','2020-12-01','2020-12-10');
insert into #Roles (id, role_name, start_date, end_date) values ('10','Sales Rep','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('11','Planner','2020-12-01','2020-12-09');
insert into #Roles (id, role_name, start_date, end_date) values ('12','Planner','2020-12-08','2020-12-12');

The query should return the following values

Error Records

Upvotes: 0

Views: 64

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

; WITH CTE AS
(
    SELECT 
        id
        , role_name
        , start_date
        , COALESCE(end_date, '2100-01-01') AS end_date
        , LAG(COALESCE(end_date, '2100-01-01')) 
             OVER (PARTITION BY role_name ORDER BY start_date) AS prev_end_date
    FROM #Roles
)
SELECT id, role_name
FROM #Roles 
WHERE role_name IN
(
    SELECT DISTINCT role_name
    FROM CTE c
    WHERE prev_end_date >= start_date
)

The CTE fetches the previous record for the same role_name. Using previous end_date value we can check for overlapping entries.

DB Fiddle Demo

Upvotes: 1

Related Questions