Listing Continus Numbers
-
I have 2 tables, in which first table contain a limit and
the second one contains numbers
For eg:-
First Table contain follwoing Structure
StartNo ENDNo
-----------------------
1 10
Second One
Numbers
-----------
5
6
3
I need to generate Numbers which is not in second table and not
exceed the limit specified in first table?
Expected result:-
1
2
4
7
8
9
10
I used following query, is any other simple way
DECLARE @TT1 TABLE
(
StartNo INT,
EndNoINT
)
DECLARE @TT2 TABLE
(
DDNo INT
)
DECLARE @Tmp INT
INSERT INTO @TT1
SELECT 1,10
INSERT INTO @TT2
SELECT 5
UNION
SELECT 6
UNION
SELECT 3
;WITH CTE
AS
(
SELECT StartNo,EndNo FROM @TT1
UNION ALL
SELECT A.StartNo+1,A.EndNo
FROM CTE A
INNER JOIN @TT1 B
ON A.StartNo < B.EndNo
)
SELECT TOP 1 @Tmp = StartNo FROM CTE
WHERE StartNo NOT IN (SELECT DDNo FROM @TT2)
Order by StartNo
SELECT @Tmp '@Tmp'
-
why are you doing this ?
I mean, the +1 , and then inserting to another table...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Using a numbers/tally table
http://www.sqlservercentral.com/articles/T-SQL/62867/
SELECT t.NFROM dbo.Tally t
WHERE EXISTS(SELECT * FROM @TT1 t1 WHERE t.N BETWEEN t1.StartNo AND t1.EndNo)
EXCEPT
SELECT DDNo
FROM @TT2
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply