CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Adam
Adam

Reputation: 1715

How to find range of a number where the ranges come dyamically from another table?

If I had two tables:

PersonID | Count
-----------------
1        | 45
2        | 5
3        | 120
4        | 87
5        | 60
6        | 200
7        | 31

SizeName | LowerLimit
-----------------
Small    | 0
Medium   | 50
Large    | 100

I'm trying to figure out how to do a query to get a result similar to:

PersonID | SizeName
-----------------
1        | Small
2        | Small
3        | Large
4        | Medium
5        | Medium
6        | Large
7        | Small

Basically, one table specifies an unknown number of "range names" and their integer ranges associated. So a count range of 0 to 49 from the person table gets a 'small' designation. 50-99 gets 'medium' etc. But I need it to be dynamic because I do not know the range names or integer values. Can I do this in a single query or would I have to write a separate function to loop through the possibilities?

Upvotes: 2

Views: 1328

Answers (3)

bobs
bobs

Reputation: 22224

SELECT p.PersonID, Ranges.SizeName
FROM People P
JOIN
    (
    SELECT SizeName, LowerLimit, MIN(COALESCE(upperlimit, 2000000)) AS upperlimit
    FROM (
        SELECT rl.SizeName, rl.LowerLimit, ru.LowerLimit AS UpperLimit
        FROM Ranges rl
        LEFT OUTER JOIN Ranges ru ON rl.LowerLimit < ru.LowerLimit
        ) r
    WHERE r.LowerLimit < COALESCE(r.UpperLimit, 2000000)
    GROUP BY SizeName, LowerLimit
    ) Ranges ON p.Count >= Ranges.LowerLimit AND p.Count < Ranges.upperlimit
ORDER BY PersonID

Upvotes: 1

Thomas
Thomas

Reputation: 64674

With Ranges As
    (
    Select 'Small' As Name, 0 As LowerLimit
    Union All Select 'Medium', 50
    Union All Select 'Large', 100
    )
    , Person As
    (
    Select 1 As PersonId, 45 As [Count]
    Union All Select 2, 5
    Union All Select 3, 120
    Union All Select 4, 87
    Union All Select 5, 60
    Union All Select 6, 200
    Union All Select 7, 31
    )
    , RangeStartEnd As
    (
    Select R1.Name
        , Case When Min(R1.LowerLimit) = 0 Then -1 Else MIN(R1.LowerLimit) End As StartValue
        , Coalesce(MIN(R2.LowerLimit), 2147483647) As EndValue
    From Ranges As R1
        Left Join Ranges As R2
            On R2.LowerLimit > R1.LowerLimit
    Group By R1.Name
    )
Select P.PersonId, P.[Count], RSE.Name
From Person As P
    Join RangeStartEnd As RSE
        On P.[Count] > RSE.StartValue 
            And P.[Count] <= RSE.EndValue

Although I'm using common-table expressions (cte for short) which only exist in SQL Server 2005+, this can be done with multiple queries where you create a temp table to store the equivalent of the RangeStartEnd cte. The trick is to create a view that has a start column and end column.

Upvotes: 1

kbrimington
kbrimington

Reputation: 25692

Try this out:

SELECT PersonID, SizeName
FROM
   (
   SELECT
      PersonID,
      (SELECT MAX([LowerLimit]) FROM dbo.[Size] WHERE [LowerLimit] < [COUNT]) As LowerLimit
   FROM dbo.Person
   ) A
   INNER JOIN dbo.[SIZE] B ON A.LowerLimit = B.LowerLimit

Upvotes: 4

Related Questions