Look for overlapping in a table-CodePudding

I have a table in SQL Server that stores rugs. Each one has its own ID, and a range in meters (from-to) with a color.

CREATE TABLE rugs (
  [code] VARCHAR (10), 
  [from] INT, 
  [to] INT,
  [color] VARCHAR (10)
);

INSERT INTO rugs VALUES ('RUG001', 0, 1, 'Yellow');
INSERT INTO rugs VALUES ('RUG001', 1, 2, 'Red');
INSERT INTO rugs VALUES ('RUG001', 2, 4, 'Blue');
INSERT INTO rugs VALUES ('RUG001', 3, 5, 'Green');
INSERT INTO rugs VALUES ('RUG002', 0, 1, 'Purple');
INSERT INTO rugs VALUES ('RUG002', 1, 2, 'Orange');

In this example, it is overlapping in the rows corresponding to the blue and green colors for the rug RUG001. RUG002 is OK.

How can I check, for the same rug, if there are intervals with overlapping?

What I tried (and it doesn't work)

Select * from RUGS as R1
INNER JOIN RUGS as R2
ON R1.CODE = R2.CODE
WHERE
R1.[FROM] <= R2.[TO] AND R1.[TO] >= R2.[FROM]

CodePudding user response：

Hard to be sure with a small data set but does the following work for you? Using Lead/Lag to check if the next or last range falls within the current range.

with rug as (
select * ,
    Lead([from]) over(partition by code order by [from]) nextfrom, 
    Lag([to]) over(partition by code order by [to]) lastto
from rugs
)
select code, [from], [to], color 
from rug
where nextfrom<[to] or lastto>[from]

CodePudding user response：

Here you have two possible solutions.

Just left side overlap:

SELECT * FROM RUGS R1
INNER JOIN RUGS R2 ON R1.CODE = R2.CODE
WHERE R1.[TO] < R2.[TO] AND R2.[FROM] < R1.[TO] 

--Result
/*
code    from    to  color   code    from    to  color
RUG001  2       4   Blue    RUG001  3       5   Green
*/

All overlaps:

SELECT * FROM RUGS R1
INNER JOIN RUGS R2 ON R1.CODE = R2.CODE
WHERE R1.[FROM] < R2.[FROM] AND R2.[TO] < R1.[TO] 
OR (R2.[FROM] < R1.[FROM] AND  R2.[TO] < R1.[TO] AND R1.[FROM] < R2.[TO])
OR (R1.[FROM] < R2.[FROM] AND  R1.[TO] < R2.[TO] AND R2.[FROM] < R1.[TO])

-- Result
/*
code    from    to  color   code    from    to  color
RUG001  2       4   Blue    RUG001  3       5   Green
RUG001  3       5   Green   RUG001  2       4   Blue
*/