SQL Query - Find block of sequential numbers

[Jenn L]

I have a database that is pre-populated with sequential part numbers.
As people reserve the parts I update a flag to show the # is no longer
available. Now they want the ability to take out a block of "x"
number of sequential part numbers - say for example 5.

If my database had the following numbers available:
101
104
105
110
111
112
113
114

It should return 110 thru 114 and then I would write an update query
to change the flags to 1 (checked out).

I have only been able to return the first "x" number of records - have
not been able to make sure they are stepped sequentially - with the
following:

SELECT ID_ITEM From PARTNO_CHKOUT_S PECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_S PECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OU T=0 {maxrows 5}

The above would return 101, 104, 105, 110, 111

I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and get
errors, probably incorrect syntax. Can I do this in an SQL statement?

[John Gilson]

"Jenn L" <jm******@dorne r.com> wrote in message
news:61******** *************** ***@posting.goo gle.com...

I have a database that is pre-populated with sequential part numbers.
As people reserve the parts I update a flag to show the # is no longer
available. Now they want the ability to take out a block of "x"
number of sequential part numbers - say for example 5.

If my database had the following numbers available:
101
104
105
110
111
112
113
114

It should return 110 thru 114 and then I would write an update query
to change the flags to 1 (checked out).

I have only been able to return the first "x" number of records - have
not been able to make sure they are stepped sequentially - with the
following:

SELECT ID_ITEM From PARTNO_CHKOUT_S PECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_S PECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OU T=0 {maxrows 5}

The above would return 101, 104, 105, 110, 111

I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and get
errors, probably incorrect syntax. Can I do this in an SQL statement?

CREATE TABLE PartNumbersAvai lable
(
part_number INT NOT NULL PRIMARY KEY
)

INSERT INTO PartNumbersAvai lable (part_number)
VALUES (101)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (104)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (105)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (110)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (111)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (112)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (113)
INSERT INTO PartNumbersAvai lable (part_number)
VALUES (114)

-- All part numbers P where there doesn't exist a part number P+1
-- Broken out of the next query for clarity
CREATE VIEW NoConsecutivePa rtNumbers (part_number)
AS
SELECT P1.part_number
FROM PartNumbersAvai lable AS P1
LEFT OUTER JOIN
PartNumbersAvai lable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL

-- All part number sequences
CREATE VIEW PartNumberSeque nces
(first_consecut ive, last_consecutiv e, sequence_length )
AS
SELECT MIN(part_number ),
last_consecutiv e,
last_consecutiv e - MIN(part_number ) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_num ber) AS last_consecutiv e
FROM PartNumbersAvai lable AS P1
LEFT OUTER JOIN
NoConsecutivePa rtNumbers AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutiv e

SELECT *
FROM PartNumberSeque nces
ORDER BY first_consecuti ve

first_consecuti ve last_consecutiv e sequence_length
101 101 1
104 105 2
110 114 5

-- longest sequences
SELECT *
FROM PartNumberSeque nces
WHERE sequence_length = (SELECT MAX(sequence_le ngth)
FROM PartNumberSeque nces)

first_consecuti ve last_consecutiv e sequence_length
110 114 5

--
JAG

[louis nguyen]

Hi Jenn,

I'm not clear what you want. In general, if I have a complicated
UPDATE, I like to use UPDATE FROM. I first select the items that I
want and save it in a temp table. Then JOIN using the UPDATE FROM.

Something like:
Select top 5 ID_ITEM
Into #T
From PARTNO_CHKOUT_S PECIAL
Order by ID_ITEM Descending

Update PARTNO_CHKOUT_S PECIAL
Set Flag=1
From PARTNO_CHKOUT_S PECIAL a
Join #T b
On a.ID_ITEM=b.ID_ ITEM

Which can also be rewritten as:
Update PARTNO_CHKOUT_S PECIAL
Set Flag=1
From
(Select top 5 ID_ITEM From PARTNO_CHKOUT_S PECIAL Order by ID_ITEM
Descending) b
On PARTNO_CHKOUT_S PECIAL.ID_ITEM= b.ID_ITEM

jm******@dorner .com (Jenn L) wrote in message news:<61******* *************** ****@posting.go ogle.com>...

I have a database that is pre-populated with sequential part numbers.
As people reserve the parts I update a flag to show the # is no longer
available. Now they want the ability to take out a block of "x"
number of sequential part numbers - say for example 5.

If my database had the following numbers available:
101
104
105
110
111
112
113
114

It should return 110 thru 114 and then I would write an update query
to change the flags to 1 (checked out).

I have only been able to return the first "x" number of records - have
not been able to make sure they are stepped sequentially - with the
following:

SELECT ID_ITEM From PARTNO_CHKOUT_S PECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_S PECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OU T=0 {maxrows 5}

The above would return 101, 104, 105, 110, 111

I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and get
errors, probably incorrect syntax. Can I do this in an SQL statement?

[Jennifer Lisser]

Thank you both for your quick responses!

John - I tried doing the temporary table but wasn't able to. I
neglected to mention my database is EMS/RMS and I don't have rights to
create a table, only update the one existing table. I can update and
select via standard SQL queries but am limited in other functions.
There will be thousands of part numbers in this table so I'm not sure
what my best approach would be (an array of used or available numbers
may cripple the page).

Louis - The update is easy especially if they want to take one number
for one special job. The problem is engineers want numbers in sequence.
For example, they need 25 sequential part numbers in the 75xxxx series,
somehow I would have to query the table to find 750200 thru 750224 were
available before doing the update. The logic of stepping through to see
if I have the sequence of numbers available is the tough part.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Gilson]

"Jennifer Lisser" <je************ *@dorner.com> wrote in message
news:40******** *************@n ews.frii.net...

Thank you both for your quick responses!

John - I tried doing the temporary table but wasn't able to. I
neglected to mention my database is EMS/RMS and I don't have rights to
create a table, only update the one existing table. I can update and
select via standard SQL queries but am limited in other functions.
There will be thousands of part numbers in this table so I'm not sure
what my best approach would be (an array of used or available numbers
may cripple the page).

Louis - The update is easy especially if they want to take one number
for one special job. The problem is engineers want numbers in sequence.
For example, they need 25 sequential part numbers in the 75xxxx series,
somehow I would have to query the table to find 750200 thru 750224 were
available before doing the update. The logic of stepping through to see
if I have the sequence of numbers available is the tough part.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The solution I offered does not use a temp table. The one table I created
was meant to simply be a stand-in for the table in your database that
has available part numbers. Simply substitute your table name in the
code provided and all should be fine.

--
JAG

[Erland Sommarskog]

Jennifer Lisser (je************ *@dorner.com) writes:

John - I tried doing the temporary table but wasn't able to. I
neglected to mention my database is EMS/RMS and I don't have rights to
create a table, only update the one existing table. I can update and
select via standard SQL queries but am limited in other functions.
There will be thousands of part numbers in this table so I'm not sure
what my best approach would be (an array of used or available numbers
may cripple the page).

In MS SQL Server everyone has the rights to create temp tables.

Now you mention EMS/RMS that I don't know what it is, but reviewing your
attempt to a query, I see that inlucdes syntax ("maxrows 5") which is
not legal in SQL Server. And Microsoft SQL Server is what this group is
devoted to.

In any case, you might have misunderstood the purpose of the CREATE
TABLE statement in John's posting. It is customary when you ask a question
to provide CREATE TABLE statements for the tables involved, and INSERT
statements with sample data. This increases your chances to get a good
reply. Now, the great thing with John, is that he actually does the
work for you. So from John you get a tested script which proves that
you get the desired result. But you still need to transform into your
database.

Now, John solution used views, and if you don't have privileges to
create views, you can try this query, still using his table:

SELECT *
FROM (SELECT first_consecuti ve = MIN(part_number ), last_consecutiv e,
length = last_consecutiv e - MIN(part_number ) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_num ber) AS last_consecutiv e
FROM PartNumbersAvai lable AS P1
LEFT JOIN (SELECT P1.part_number
FROM PartNumbersAvai lable AS P1
LEFT JOIN PartNumbersAvai lable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL) AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutiv e) AS P
WHERE length >= 5
ORDER BY first_consecuti ve

Here I have only expanded John's views into derived tables.

Whether EMS/RMS supports derived tables I don't know, but at least
this is a feature that is in ANSI-SQL, and not proprietary to MS SQL Server.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Gilson]

"Erland Sommarskog" <so****@algonet .se> wrote in message news:Xn******** **************@ 127.0.0.1...

Jennifer Lisser (je************ *@dorner.com) writes:

John - I tried doing the temporary table but wasn't able to. I
neglected to mention my database is EMS/RMS and I don't have rights to
create a table, only update the one existing table. I can update and
select via standard SQL queries but am limited in other functions.
There will be thousands of part numbers in this table so I'm not sure
what my best approach would be (an array of used or available numbers
may cripple the page).
In MS SQL Server everyone has the rights to create temp tables.

Now you mention EMS/RMS that I don't know what it is, but reviewing your
attempt to a query, I see that inlucdes syntax ("maxrows 5") which is
not legal in SQL Server. And Microsoft SQL Server is what this group is
devoted to.

In any case, you might have misunderstood the purpose of the CREATE
TABLE statement in John's posting. It is customary when you ask a question
to provide CREATE TABLE statements for the tables involved, and INSERT
statements with sample data. This increases your chances to get a good
reply. Now, the great thing with John, is that he actually does the
work for you. So from John you get a tested script which proves that
you get the desired result. But you still need to transform into your
database.

And this time around, you've done the work for me. Many thanks!
Glad I never miss reading an Erland post.

Warm regards,
John
Now, John solution used views, and if you don't have privileges to
create views, you can try this query, still using his table:

SELECT *
FROM (SELECT first_consecuti ve = MIN(part_number ), last_consecutiv e,
length = last_consecutiv e - MIN(part_number ) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_num ber) AS last_consecutiv e
FROM PartNumbersAvai lable AS P1
LEFT JOIN (SELECT P1.part_number
FROM PartNumbersAvai lable AS P1
LEFT JOIN PartNumbersAvai lable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL) AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutiv e) AS P
WHERE length >= 5
ORDER BY first_consecuti ve

Here I have only expanded John's views into derived tables.

Whether EMS/RMS supports derived tables I don't know, but at least
this is a feature that is in ANSI-SQL, and not proprietary to MS SQL Server.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp