Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get "available" numbers

Status
Not open for further replies.

microsGuy16

Programmer
May 26, 2016
25
US
I have a table that contains a list of products. One of the columns contains a product number (it is not an identity column). I would like to create a stored proc that will give me all available product numbers inside a range. So for example, my table may have 10 records and the barcode numbers are 101, 102, 104, 105, 107, 108, 109, 111, 112, 113.... I would like to call a stored proc with the parameters StartProductNumber of 101 and EndProductNumber 113. The results would give me 103, 106, and 110 since they do not exist. What's the easiest way to accomplish this?​
 
A simple query does this:

Code:
SELECT * FROM products where barcode between 101 and 113

or parameterized:
Code:
lnProductBarcodeLow = 101
lnProductBarcodeHigh = 113
SELECT * FROM products where barcode between ?m.lnProductBarcodeLow and ?m.lnProductBarcodeHigh

Bye, Olaf.
 
the query needs to give me the numbers that are missing from the sequence
 
I expect you could use something like this where you create a table of all values and left join your product number:

SQL:
WITH
cteNums As
(SELECT ones.n + 10*tens.n + 1000 Num
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5)                ) tens(n)
WHERE ones.n + 10*tens.n + 1000 BETWEEN 101 AND 113)
SELECT Num 
FROM cteNums LEFT JOIN [YourTableName] ON Num = SomeField
WHERE [somefield] is Null

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Ok, my bad not reading thoroughly.

Then a tally table stored proc should generate the full list 101...113 and you could do an EXCEPT query with the products.

Code:
Declare @products as Table (barcode int);


--barcodes 101..113 with several gaps
Insert into @products values (102),(104),(105),(106),(109),(110),(113);

WITH Tally (num) AS
(
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)

Select num from Tally where num between 101 and 113
Except 
Select barcode from @products where barcode between 101 and 113

If you need more than 1000 tally numbers see
If you know you never are interested in more than 1000 consecutive numbers, you don't need any advanced tally table procedure, but the low value might be much higher (as in 12 digit barcodes). For that you can also use lowvalue+ROWNUMBER()-1, of course and num doesn't start at 1.

Bye, Olaf.
 
(Untested) but what about....

Code:
SELECT DISTINCT n=number FROM master..[spt_values] WHERE Number BETWEEN 101 and 113
EXCEPT
Select barcode from @products WHERE barcode between 101 and 113



Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top