CODEWITHSUNDEEP
sqlsql-server-2008-r2
Jean Jimenez
Jean Jimenez

Reputation: 4742

SQL query to Identify number position within a range table

Hi everybody i have to make a query to determine the range that a number belongs to in a sql table, what i'm doing is using a between but when you use a value that applies to the last range where the final value is null it does not bring results, i found a workaround but i think that this is not the best way to do it. Below is an example of what i'm trying to do.

-- Creando temporal 
CREATE TABLE #TMP
(
    RangeID         INT 
    ,InitialValue   INT NULL
    ,FinalValue     INT NULL
)


-- Insertar valores
INSERT INTO #TMP
    (   RangeID, InitialValue, FinalValue   )
VALUES
    (   1, 100, 200 )
    ,(  2, 201, 300 )
    ,(  3, 301, NULL )


-- Verificando temporal
SELECT * FROM #TMP 

-- Verificando los rangos
SELECT 
    * 
FROM 
    #TMP
WHERE
    301 between     InitialValue 
    AND FinalValue

--RETURNS NOTHING

SELECT 
    * 
FROM 
    #TMP
WHERE
    301 between     InitialValue 
    AND isnull(FinalValue,301+1) 


--RETURNS THE DESIRED RESULT 

-- Borrando temporal
DROP TABLE #TMP

The problem is that when the final value is null i have to use the isnull to validate and assign a value to it.

Is there any other way to approach to this problem.

Upvotes: 0

Views: 134

Answers (2)

geomagas
geomagas

Reputation: 3280

If you insist on having NULL as the final value, then no, there's no other way than checking for nulls, in general.

But you could do

INSERT INTO #TMP
    (   RangeID, InitialValue, FinalValue   )
VALUES
    (   1, 100, 200 )
    ,(  2, 201, 300 )
    ,(  3, 301, 2147483647 )

2147483647 being the maximum integer possible.

EDIT: I just noticed, however, that i.e. 302 would fail your test.

Upvotes: 0

Matt
Matt

Reputation: 1155

Couple of options:

WHERE 301 between InitialValue AND isnull(FinalValue,9999) 
--Some value high enough to encompass all possible values

WHERE 301 >= Initial Value AND (301 <= FinalValue OR FinalValue IS NULL)
--Instead of between, use >= AND <=, and make exception for null upper limit

Upvotes: 2

Related Questions