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