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_SPECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_SPECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OUT=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******@dorner.com> wrote in message
news:61**************************@posting.google.c om...

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_SPECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_SPECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OUT=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 PartNumbersAvailable
(
part_number INT NOT NULL PRIMARY KEY
)

INSERT INTO PartNumbersAvailable (part_number)
VALUES (101)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (104)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (105)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (110)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (111)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (112)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (113)
INSERT INTO PartNumbersAvailable (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 NoConsecutivePartNumbers (part_number)
AS
SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
PartNumbersAvailable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL

-- All part number sequences
CREATE VIEW PartNumberSequences
(first_consecutive, last_consecutive, sequence_length)
AS
SELECT MIN(part_number),
last_consecutive,
last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
NoConsecutivePartNumbers AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutive

SELECT *
FROM PartNumberSequences
ORDER BY first_consecutive

first_consecutive last_consecutive sequence_length
101 101 1
104 105 2
110 114 5

-- longest sequences
SELECT *
FROM PartNumberSequences
WHERE sequence_length = (SELECT MAX(sequence_length)
FROM PartNumberSequences)

first_consecutive last_consecutive 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_SPECIAL
Order by ID_ITEM Descending

Update PARTNO_CHKOUT_SPECIAL
Set Flag=1
From PARTNO_CHKOUT_SPECIAL a
Join #T b
On a.ID_ITEM=b.ID_ITEM

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

jm******@dorner.com (Jenn L) wrote in message news:<61**************************@posting.google. 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_SPECIAL M Where (Select Count(*)
FROM PARTNO_CHKOUT_SPECIAL N
WHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 AND
FLAG_CHECKED_OUT=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*********************@news.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_consecutive = MIN(part_number), last_consecutive,
length = last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT JOIN (SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT JOIN PartNumbersAvailable 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_consecutive) AS P
WHERE length >= 5
ORDER BY first_consecutive

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_consecutive = MIN(part_number), last_consecutive,
length = last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT JOIN (SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT JOIN PartNumbersAvailable 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_consecutive) AS P
WHERE length >= 5
ORDER BY first_consecutive

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