SQL query with cursor?

[SQLHacker]

I'm using SQL Server 2005 and need to figure out how to write a query that will show me missing ID numbers.

The number series is "2000xxx" and I've found a problem with the front end .asp page that's allowing users to skip adding user ids. Anyway, rather than manually look through all of the numbers to find gaps, I'm sure there's some way to find missin sequential numbers in this field. However, the field datatype is varchar(15), so I don't know if that will create problems looking for sequential numbers.

This is a single table where the value is stored, called tPerson, and the field name is PersonID.

Can anyone help?

P.S. I've never written a cursor query, so please provide as much detail as possible.

Thanks!

 

[SQLDenis]

create a number table with numbers between 0 and your max number
left join with your table make your where clause

WHERE PersonID IS NULL
This will give you the result you want/need

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLHacker]

Denis,

That's only part of what I need. I can easily find the people who don't have a PersonID, but I need to know where the gaps are in the number sequence (to make sure there aren't unused numbers) and apply them to the people who have NULL PersonIDs first, and then find the maximum number and begin using the sequential numbers from there.

So, of the existing PersonIDs, I need to search through them to find any "gaps" between the sequential numbers. For example, if there is a "2000100" and "2000102" I need something that will find that "2000101" is missing.

 

[gmmastros]

That is what Denis suggested. I think you misunderstood it.

Suppose you have a numbers table in your database. Also, suppose it starts at 2000000 and goes to 2000999.

Now, when you left join on this table (with the tPerson table), you will get a list of ID's that don't match (and represents the gaps.

[tt][blue]
Select Numbers.Number
From Numbers
Left Join tPerson
On Numbers.Number = tPerson.PersonId
Where tPerson.PersonId Is NULL
[/blue][/tt]

If there are NULL PersonId's in the tPerson table, they will NOT bet returned by this query.

Does this make sense now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Lets do the Denis' suggestion with example:

DECLARE @Test TABLE (PersonId int)
DECLARE @i int
SET @i = 2000100
WHILE @i < 2000103
      BEGIN
         INSERT INTO @Test VALUES (@i)
         SET @i = @i + 1
      END

SELECT Test.PersonId AS MissingIDs
       FROM @Test Test
LEFT JOIN Person ON Test.PersonId = Person.PersonID
WHERE Person.PersonID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLHacker]

That is fantastic! I don't understand how that works, but it does!

Thank you all very much!!

 

[gmmastros]

>> I don't understand how that works

Do a little research on 'left join'. If you still don't understand it, then post back and I'll explain it for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom