coming up with a routine

G

Guest

hi

I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to
create an routine that can detect if a ticket is missing so I just need to
detect if the secuence is broken (1,2,3,5... 4 is missing)

I want to create a good routine and I can only thing about a for loop with
IF THEN inside

Any one have an idea?

Can I do it with an Store procedure insted a C# code?
 
K

Ken Dopierala Jr.

Hi Kenny,

I would instead have my table use an identity field for the ticket number
that auto-increments by 1. This way you would never have a number missing
from the sequence. Also in the table create a field for a flag as to
whether a ticket was cancelled or something. Then you can do a quick query
for all non-valid tickets just by checking this field. Good luck! Ken.
 
J

Jeff Louie

Kenny... The first question is Is it worth optimizing this step? If the
answer is
yes, then you could possibly speed up the processing by returning a
sorted
sequence and writing your own algorithm doing a binary search looking
for
row number > ticket number where row number and ticket number are one
based.

This begs the question if your DB truly is guaranteed to create a
sequence.
Identity does _not_ quarantee a valid sequence. "The value automatically
produced with the Identity .... is not guaranteed to be consecutive. For
efficiency, a value is considered used as soon as it is presented to a
client."

So if a client starts an INSERT and then rolls back on error the number
is
_lost_ and a gap appears. If you want a consecutive sequence consider a
"next_number" table.

As for SQL.
http://www.developersdex.com/gurus/articles/115.asp
http://www.nigelrivett.net/FindGapsInSequence.html
http://www.tek-tips.com/faqs.cfm?fid=840

Finding gaps in an identity column sequence

Suppose a table has an identity column and gaps exist in the identity
sequence. Suppose you want to identify the gaps and fill them in. (I
don't
know why one would want to do this but we are just supposing. ) The
following T-SQL code will find the next gap in the sequence.

Create Procedure usp_FindNextPartID As

Declare @MinID int
Select @MinID = Min(PartID) From PartTable

If @MinID > 1
  Select MinID=1
Else
  Select MinID=min(a.PartId+1)
   From PartTable a Left Join PartTable b
         On a.PartId+1=b.PartId
    Where b.PartNumber Is Null
Go


Regards,
Jeff
I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to
create an routine that can detect if a ticket is missing so I just need
to
detect if the secuence is broken (1,2,3,5... 4 is missing)<
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top