CODEWITHSUNDEEP
sqlsql-server
abuybuy
abuybuy

Reputation: 819

Range type parameter in SQL Server

i need a help for my problem. I have a value to be stored in table, and the value is a range.

ex:

Category             Value
-------------------- ---------------------------
1                    < 70
2                    > 70 - 90
3                    > 90 - 100
4                    > 100 - 120
5                    > 120

so, when i have a value 80, he is in category 2.

ex:

Value                Category
-------------------- ---------------------------
80                    2 
75                    2 
115                   4 
50                    1 
99                    2

How is the easiest way to store this range type parameter in table, and easy to query it?

Thank you in advance

Upvotes: 0

Views: 1779

Answers (4)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Prepare data

Declare @category as table(categoryid int,fromvalue int, tovalue int)
Declare @Value as table(value int)

insert into @category values
(1,NULL,70  ),
(2,70 ,90   ),
(3,90 ,100  ),
(4,100, 120 ),
(5,120,null )

insert into @Value values
(80 ),  
(75 ),  
(115),  
(50 ),  
(99 )

Query 

SELECT v.value,c.categoryid
FROM @category C INNER JOIN @Value V 
            on (v.value >=c.fromvalue or c.fromvalue is null) 
                and (v.value<=c.tovalue or c.tovalue is null)

Upvotes: 1

Eric
Eric

Reputation: 5743

Adding to Felix's solution, if the ranges were continuous, you can have the flawless design of range table

-- Accept only one value of range, RangeTo
DECLARE @ContinuousRange table (CategoryId int identity(1,1), RangeTo int primary key)
INSERT @ContinuousRange VALUES (70), (90), (100), (120) 
,(2147483647) -- Add this to the last entry for completing the range set

-- Your table
DECLARE @Values table (Value int)
INSERT INTO @Values VALUES (80), (75), (115), (50), (99)

-- Usage
SELECT * 
FROM @Values v 
    OUTER APPLY 
    (
       SELECT TOP 1 * FROM @ContinuousRange WHERE v.value <= RangeTo 
       ORDER BY RangeTo
    ) rng

Upvotes: 1

InfoSolve Pty Ltd
InfoSolve Pty Ltd

Reputation: 3

Depends on what UI you have in mind. A stored procedure will help you return the result.

The following link outlines returning values: https://msdn.microsoft.com/en-AU/library/ms188655.aspx

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

I would design it this way.

CREATE TABLE CategoryRange(
    CategoryId  INT IDENTITY(1, 1),
    MinValue    INT NULL,
    MaxValue    INT NULL
)

To get the corresponding category:

Note that range is interpreted as value >= min AND value < max

DECLARE @values TABLE(value INT)
INSERT INTO @values VALUES
(80), (75), (115), (50), (99)

SELECT
    v.value,
    r.CategoryId
FROM @values v
CROSS APPLY(
    SELECT CategoryId
    FROM CategoryRange
    WHERE
        (MinValue IS NULL OR v.value >= MinValue)
        AND (
            MaxValue IS NULL
            OR v.value < MaxValue
        )
)r

SQL Fiddle

| value | CategoryId |
|-------|------------|
|    80 |          2 |
|    75 |          2 |
|   115 |          4 |
|    50 |          1 |
|    99 |          3 |

Upvotes: 1

Related Questions