Very Computer

We have a table like this

CREATE TABLE Widgets
(WidgetNumber int,
WidgetID varchar(50))
GO

The Table is filled with sequential data in the Widget ID column that
always ends in a number, i.e. A0001, A0002, A0003, etc.

I need to create procedure where I can input the start ID and get the
last sequential value, up to X records, where X is another input.
i.e.

as

--Some Nifty code goes here

Go

i.e. if I have Widget IDs of '1','2','3','4','6','7','8' etc...

and I

go

I have set up a cursor that checks cur value vs last value, but, as
you can guess it is slooooww, esp with hundreds of thousands of
records.

It's a little more complicated than this, but this is the basic idea.

Any suggestions would be greatly appreciated!

Thanks,

Tom

Thanks for the table schema!

However, in the future, please ALWAYS post which version of SQL Server you
are using, and also please post sample data as INSERTs.

I'm not positive that I fully understand your request, but you might be able
to adapt the following code, which is designed for SQL Server 2000 ...
although you can substitute a temp table for the table variable if you are
not using SQL Server 2000:

CREATE TABLE Widgets
(WidgetNumber int,
WidgetID varchar(50))
GO

insert into Widgets values (1, 'A0001')
insert into Widgets values (1, 'A0002')
insert into Widgets values (1, 'A0003')
insert into Widgets values (1, 'A0004')
insert into Widgets values (1, 'A0006')
insert into Widgets values (1, 'A0007')
insert into Widgets values (1, 'A0008')
insert into Widgets values (1, 'A0009')

select WidgetID
from Widgets

set rowcount 0

select top 1 WidgetID

order by WidgetID desc

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Thanks for the suggestion, I'll try to remember to post more
information next time, it all made perfect sense to me!

Anyway, I am using SQL 2000.  I actually found a post from Umachandar
Jayachandran in the microsoft.public.sqlserver.programming group with
the following suggestion for a similar problem someone had back in
2000:

(that message can be accessed here:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23v%24%23efZ...)

select a.seq
from tbl a
where not exists(select * from tbl b
                 where b.seq - a.seq = 1 ) and
      a.seq <> ( select max( seq ) from tbl )

This gives all the records that are followed by a gap.  I tweaked this
so I could search between a start and end value and, since my ID
column is varchar, but I know that the right 4 characters are always
numbers, my complete code looks something like this:

CREATE TABLE Widgets
(WidgetNumber int,
WidgetID varchar(50))
GO

insert into Widgets values (1, 'A0001')
insert into Widgets values (1, 'A0002')
insert into Widgets values (1, 'A0003')
insert into Widgets values (1, 'A0004')
insert into Widgets values (1, 'A0006')
insert into Widgets values (1, 'A0007')
insert into Widgets values (1, 'A0008')
insert into Widgets values (1, 'A0009')
go

varchar(50) output
AS

FROM Widgets A
WHERE Not Exists(SELECT * FROM Widgets B WHERE
convert(int,right(B.WidgetID,4)) - convert(int,right(A.WidgetID,4)) =
1

A.WidgetID <> ( SELECT max( WidgetID ) FROM Widgets ) And

ORDER BY A.WidgetID

GO

go

The results are:

--------------------------------------------------
A0004

(1 row(s) affected)

I can now use this as the basis to find a series of complete sequences
until I get X widgets and their sequences.  Basically, our customer
ships out the widgets and needs to keep track of the IDs that were
sent. Rather than a verbose listing (since they send out thousands at
a time), this allows them to get a list of the sequences of WidgetIDs
that were sent, so if they send out 10 widgets, they can get
A0001 - A0004
A0006 - A0011

This is a simplification, but the general idea is there.

Anyway, thanks again, hope this helps someone else down the line!