Click here to Skip to main content
15,887,175 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
if we have table customers
and have like that

custCode
1
2
4
7
10

if i select count i will get 5
else if i select max i will get 10
i want select 3,5,6,8,9
the numbers that not founded in the table
thanks for every one
Posted

1 solution

Hi,
SQL
DECLARE @LIMIT INT = 10
;WITH CTE_Series AS
(
    SELECT 1 [Sequence] UNION ALL
    SELECT [Sequence] + 1 FROM CTE_Series WHERE [Sequence] < @LIMIT
)
SELECT [Sequence] AS SkippedCustCodes FROM CTE_Series
EXCEPT
SELECT custCode FROM customers

Happy Coding!
:)
 
Share this answer
 
Comments
King Fisher 27-Mar-14 5:54am    
my +5!
Aarti Meswania 27-Mar-14 7:14am    
Thank you! King_Fisher
Tom Marvolo Riddle 27-Mar-14 5:55am    
Good one.I tried to upvote but vote is not casting.Sorry for that :((issues resolved) +5!
Aarti Meswania 27-Mar-14 7:14am    
Thank you! Jas 24 :)
Andrius Leonavicius 27-Mar-14 5:56am    
Nice, +5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900