CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user3511908
user3511908

Reputation: 1

How to find overlapping date ranges from same tables (multiple overlapping)

there are user data with data range data

I'm trying to update flag section by using query. If same user id group overlap any single day range, it should say overlapping in flag section.

can you please give me some idea how to flag this overlapping data Thanks

Text file format:

ID UserID   registereddate  termdate    flag
1   abcd    1/1/2018    2/28/2018   overlapping with 2
2   abcd    1/1/2018    6/30/2018   overlapping with 1
3   abcd    8/1/2018    12/31/2018  
4   bbbb    5/1/2018    6/30/2018   overlapping with 5
5   bbbb    6/1/2018    7/30/2018   overlapping with 4
6   bbbb    9/1/2018    9/30/2018   
7   bbbb    10/1/2018   10/30/2018  
8   bbbb    11/1/2018   11/30/2018  
9   ccccc   7/1/2018    9/30/2018   overlapping with 10
10  ccccc   9/1/2018    12/31/2018  overlapping with 9
11  dddd    8/1/2018    8/31/2018   
12  dddd    12/1/2018   12/31/2018  
13  eeee    9/1/2018    12/31/2018  overlapping with 17
14  eeee    8/1/2018    8/31/2018   
15  eeee    9/1/2018    9/30/2018   overlapping with 15

Upvotes: 0

Views: 1750

Answers (2)

Joe van de Bilt
Joe van de Bilt

Reputation: 265

You could also inner join the table onto itself and compare the dates that way 

SELECT * 
FROM [Table] t1
INNER JOIN [Table] t2 
ON  t1.ID <> t2.ID
AND t1.UserId = t2.UserId
AND ((t1.RegisterDate BETWEEN t2.RegisterDate AND t2.TermDate) OR (t1.TermDate BETWEEN t2.RegisterDate AND t2.TermDate))

Although the more field you end up having the more complicated this becomes

See this DBFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

To get 'overlapping', use exists:

select t.*,
       (case when exists (select 1
                          from t t2
                          where t2.registereddate < t.termdate and
                                t2.termdate > t.registereddate
                         )
             then 'overlaps'
        end)
from t;

In an update, this looks like:

update t
    set flag = 'overlaps'
    where exists (select 1
                  from t t2
                  where t2.registereddate < t.termdate and
                        t2.termdate > t.registereddate
                 );

Getting the list of overlapping records in a string field is much, much more complicated in SQL Server. Getting a pairwise list of overlaps is pretty easy.

Upvotes: 2

Related Questions