How to extract all numbers between two numbers using single query

  • Need Help !!!!!!!

    How can I extract all numbers between two limits using a query

    eg :

    my table HouseDetails

    AgentID FromNumber ToNumber

    1 11 16

    2 20 24

    1 20 22

    For AgentID 1, It should return 11,12,13,14,15,16,20,21,22

    AgentID 2, It should return 20,21,22,23,24

    Is it possible ? Anybody ...!!!

  • Probably the best way would be to create a "numbers" or "tally" table. Search this site for how to do that and examples of what it can be used for. I'm sure you will then be able to tailor it to meet your own needs.

    John

  • A tally table will give you the best performance, but I figured I would try this with a recursive CTE. Here is a way to do it:

    [font="Courier New"] /* Sample Data */

    CREATE TABLE #tmp

    (AgentID INT, FromNumber INT, ToNumber INT)

    INSERT #tmp VALUES (1,11,16)

    INSERT #tmp VALUES (2,20,24)

    INSERT #tmp VALUES (1,20,22)

    /* Query */

    ; WITH AgentInfo (AgentID, MinNumber, MaxNumber, CurNumber)

    AS

    (

    SELECT AgentID, Min(FromNumber), Max(ToNumber), Min(FromNumber)

    FROM #tmp

    GROUP BY AgentID

    UNION ALL

    SELECT AgentID, MinNumber, MaxNumber, CurNumber + 1

    FROM AgentInfo

    WHERE CurNumber < MaxNumber

    )

    SELECT

    *

    FROM

    AgentInfo

    ORDER BY

    1, 4[/font]

  • In case you need the numbers concatenated

    DECLARE@Sample TABLE (AgentID INT, FromNumber INT, ToNumber INT)

    INSERT@Sample

    SELECT1, 11, 16 UNION ALL

    SELECT2, 20, 24 UNION ALL

    SELECT1, 20, 22

    ;WITH Yak (AgentID, FromNumber, ToNumber)

    AS (

    SELECTAgentID,

    FromNumber,

    ToNumber

    FROM@Sample

    UNION ALL

    SELECTAgentID,

    FromNumber + 1,

    ToNumber

    FROMYak

    WHEREFromNumber < ToNumber

    )

    SELECT DISTINCTs.AgentID,

    STUFF(n.Numbers, 1, 1, '') AS Numbers

    FROM@Sample AS s

    CROSS APPLY(

    SELECT DISTINCTTOP 100 PERCENT

    ',' + CAST(y.FromNumber AS VARCHAR(11))

    FROMYak AS y

    WHEREy.AgentID = s.AgentID

    ORDER BY',' + CAST(y.FromNumber AS VARCHAR(11))

    FOR XMLPATH('')

    ) AS n(Numbers)

    ORDER BYs.AgentID

    Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254


    N 56°04'39.16"
    E 12°55'05.25"

  • 🙂 Great.. Thanks for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply