Sequence order

[Compkitty]

I'm looking to find gaps in sequences..

Ie.
DATA ID
1
2
3
4
6
7
8
9
A

I have 10 lines, w/ the number 5 missing, so I want to find which is missing then replace the A w/ the missing number. Else Delete the A...

Any Ideas??? THANKS ahead of time

 

[micha123]

OK...

This is because BlockId is char or varchar, right...?

Try to change that:

SELECT NPA , NXX, char(ascii(BlockId)+1) AS BlockID
INTO #tmp
FROM #TmpLergConvert t1
WHERE not exists (SELECT 1 FROM #TmpLergConvert t2 
                 WHERE t2.NPA = t1.NPA
                   AND t2.NXX = t1.NXX
                   AND ascii(t2.Blockid)+1 = ascii(t1.Blockid))
AND t1.Blockid < '9' and t1.BlockId <> 'A'

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.