Get "available" numbers

[microsGuy16]

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?​

 

[Olaf Doschke]

A simple query does this:

SELECT * FROM products where barcode between 101 and 113

or parameterized:

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

Bye, Olaf.

 

[microsGuy16]

the query needs to give me the numbers that are missing from the sequence

 

[dhookom]

I expect you could use something like this where you create a table of all values and left join your product number:

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

 

[Olaf Doschke]

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.

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

http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/

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.

 

[TamarGranor]

I wrote about this problem in this article:

http://www.tomorrowssolutionsllc.com/Articles/Including missing data.pdf

Tamar

 

[gbaughma]

(Untested) but what about....

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