Query to find out overlapping decimal ranges

1

I need to find out overlapping decimal ranges. Below is my table structure.

id FromValue ToValue Condition
1 10 15 Between
2 16 NULL Equals
3 6 9 Between
4 17 19 Between
5 16 NULL Greater Than.

When I insert row 5. I should not be able to insert because there is already a range exists between 17 to 19. When I try to insert Greater than 16 It should not allow.

Same condition applies with Less Than condition. If I try to insert less 6. It should not allow me to insert because 6 to 9 range already exists.

Please help me out with SQL Query.

2

Hi,

Hope this help you:

Case 1

Declare @tbl table(
id int,
FromValue int,
ToValue int,
Condition nvarchar(200)
)
insert into @tbl
select id=1,FromValue=10,ToValue=15,Condition='Between' union
select 1,10,15,'Between' union
select 2,16,NULL,'Equals' union
select 3,6,9,'Between' union
select 4,17,19,'Between'

insert into @tbl
select k.* from(
SELECT id=5,FromValues=6,ToValues=null,Condition='Greater Than'
) k
WHERE
NOT EXISTS (
SELECT * from @tbl
WHERE
k.FromValues = FromValue
union
SELECT * from @tbl
WHERE k.FromValues between FromValue and ToValue
union
SELECT * from @tbl
WHERE k.ToValues between FromValue and ToValue
)

SELECT * from @tbl

Case 2:

Declare @tbl table(
id int,
FromValue int,
ToValue int,
Condition nvarchar(200)
)
insert into @tbl
select id=1,FromValue=10,ToValue=15,Condition='Between' union
select 1,10,15,'Between' union
select 2,16,NULL,'Equals' union
select 3,6,9,'Between' union
select 4,17,19,'Between'

insert into @tbl
select k.* from(
SELECT id=5,FromValues=17,ToValues=19,Condition='Greater Than'
) k
WHERE
NOT EXISTS (
SELECT * from @tbl
WHERE
k.FromValues = FromValue
union
SELECT * from @tbl
WHERE k.FromValues between FromValue and ToValue
union
SELECT * from @tbl
WHERE k.ToValues between FromValue and ToValue
)

SELECT * from @tbl

Thanks.

Regards,
Mic

3

Maybe this:

INSERT INTO @tbl
SELECT Id, FromValue, ToValue, Condition
FROM (
    VALUES(5,16,NULL,'Greater Than'),(6, 6, NULL,'Less Than')
) AS test_data(Id, FromValue, ToValue, Condition)
WHERE NOT EXISTS(
    SELECT 1
    FROM @tbl t
    WHERE test_data.FromValue <= ISNULL(t.ToValue, 2147483647) AND
        ISNULL(test_data.ToValue, 2147483647) >= t.FromValue
    )