May 28, 2008 at 4:51 am
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 ...!!!
May 28, 2008 at 4:58 am
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
May 28, 2008 at 5:58 am
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]
May 28, 2008 at 6:39 am
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"
May 28, 2008 at 12:10 pm
🙂 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