I have two integer ranges and need to determine if they overlap. One or both ranges could be open-ended. Anybody have a T-SQL function that serves this purpose?
3 Answers
Shorter, hackier solution (set nulls to min/max values):
SET @p_start_range1 = ISNULL(@p_start_range1, -2147483648);
SET @p_end_range1 = ISNULL(@p_end_range1, 2147483647);
SET @p_start_range2 = ISNULL(@p_start_range2, -2147483648);
SET @p_end_range2 = ISNULL(@p_end_range2, 2147483647);
SELECT @Result =
CASE
WHEN @p_start_range1 > @p_end_range2 OR @p_start_range2 > @p_end_range1
THEN 0
ELSE 1
END;
Comments
-- Takes in two different ranges and checks to see if they overlap (inclusive).
-- A null value for a parameter implies that the range is open-ended.
-- False is returned for a malformed range (end < start).
CREATE FUNCTION dbo.uf_IntRangesOverlap(
@p_start_range1 int,
@p_end_range1 int,
@p_start_range2 int,
@p_end_range2 int)
RETURNS bit
AS
BEGIN
DECLARE
@Result bit;
IF @p_start_range1 > @p_end_range1
OR @p_start_range2 > @p_end_range2
BEGIN
-- malformed range
SELECT @Result = 0;
END;
ELSE
BEGIN
IF(@p_start_range1 IS NULL
AND @p_end_range1 IS NULL)
OR (@p_start_range2 IS NULL
AND @p_end_range2 IS NULL)
BEGIN
SELECT @Result = 1;
END;
ELSE
BEGIN
IF @p_start_range1 IS NULL
BEGIN
SELECT @Result = CASE
WHEN(@p_start_range2 IS NULL
OR @p_start_range2 <= @p_end_range1)THEN 1
ELSE 0
END;
END;
ELSE
BEGIN
IF @p_end_range1 IS NULL
BEGIN
SELECT @Result = CASE
WHEN(@p_end_range2 IS NULL
OR @p_end_range2 >= @p_start_range1)THEN 1
ELSE 0
END;
END;
ELSE
BEGIN
IF @p_start_range2 IS NULL
BEGIN
SELECT @Result = CASE
WHEN(@p_start_range1 IS NULL
OR @p_start_range1 <= @p_end_range2)THEN 1
ELSE 0
END;
END;
ELSE
BEGIN
IF @p_end_range2 IS NULL
BEGIN
SELECT @Result = CASE
WHEN(@p_end_range1 IS NULL
OR @p_end_range1 >= @p_start_range2)THEN 1
ELSE 0
END;
END;
ELSE
BEGIN
SELECT @Result = CASE
WHEN(@p_end_range1 >= @p_start_range2
AND @p_start_range1 <= @p_end_range2)THEN 1
ELSE 0
END;
END;
END;
END;
END;
END;
END;
RETURN @Result;
END;
Comments
Use the max/min values for integer:
DECLARE @p_start_range1 int
,@p_end_range1 int
,@p_start_range2 int
,@p_end_range2 int;
DECLARE @Result bit;
DECLARE @IntMin int = -2147483648;
DECLARE @IntMax int = 2147483647;
SELECT @Result = CASE
WHEN (ISNULL(@p_start_range1,@IntMin) <= ISNULL(@p_end_range2,@IntMax))
AND(ISNULL(@p_start_range2,@IntMin) <= ISNULL(@p_end_range1,@IntMax))
THEN 1
ELSE 0 END;
SELECT @Result;
@p_start_range1 <= @p_end_range1and@p_start_range2 <= @p_end_range2?