Find rows in table that fall under minimum and maximum range.

Question

Following is the table and script of this table.

DECLARE @temp TABLE (PPId INT, SVPId INT, Minimum INT, Maximum INT)

INSERT INTO @temp VALUES(1,1,8,20)
INSERT INTO @temp VALUES(2,1,21,100)

Minimum & Maximum are passed in as parameter. I want to find all rows that fall in the given range.

E.g.;

• If @minimum = 9 and @maximum = 15 then it falls in the range of first row.
• If @minimum = 21 and @maximum = 22 then it falls in the range of 2nd row.
• If @minimum = 7 and @maximum = 25 then it falls in the range of both rows so both rows should be returned.

Thanks.

Answer 1

When comparing ranges like this, it's easier to look for the case where ranges don't overlap. There are many different ways that two ranges can overlap, but there is only two ways that the don't overlap:

select *
from @temp
where not (@maximum < Minimum or @minimum > Maximum)

Answer 2

SELECT  *
FROM    @temp
WHERE   minimum <= @max
        AND maximum >= @min

Answer 3

My suggested answer is so simple I suspect either I'm missing something or the question is not complete?

SELECT *
FROM @temp
WHERE Minimum < @Minimum
    AND Maximum > @Maximum

Answer 4

I can see what you're trying to do. You want to know how many min/max ranges overlap with the provide min/max range. Try this:

SELECT * FROM @temp T
WHERE  @minimum  BETWEEN T.minimum AND T.maximum
OR     @maximum  BETWEEN T.minimum AND T.maximum
OR     T.minimum BETWEEN @minimum  AND @maximum
OR     T.maximum BETWEEN @minimum  AND @maximum

This should return all rows that intersect with the interval [@minimum, @maximum].