September 11, 2007 at 10:39 am
I am new to SQL query writing and have inherited a problem. Our company produces software that allows the end users to assign a barcode number to a customer record. The field name is 'Barcode' and it is a varchar type and it allows NULLS. What I need to do is find gaps in the numbering sequence and fill these gaps. I need to find the start of the gap and the end and then assign the numbers in between to new records.
Example:
Existing Barcodes
100,101,102,103,104,105,110,111,112,113,120,121
Need to assign new records these Barcodes
106,107,108,109,114,115,116,117,118,119
The end user has total control of asssigning the original barcode and the barcode length could be up to 12.
Any and all assistance is welcome but please keep in mind I inherited this and did NOT design it.
Clarence VanDyke
September 11, 2007 at 11:30 am
here's an example that i based off of one of Jeff Modens examples...hey I'm learning:
there'sa huge advantage performance wise to use a tally or numbers table.
--Your Sample Table :
CREATE TABLE ExistingBarCodes(SomeStuff varchar(30), Barcode int )
Insert into ExistingBarCodes(Barcode)
SELECT 100
UNION SELECT 101
UNION SELECT 102
UNION SELECT 103
UNION SELECT 104
UNION SELECT 105
UNION SELECT 110
UNION SELECT 111
UNION SELECT 112
UNION SELECT 113
UNION SELECT 120
UNION SELECT 121
--copied shamelessly from a Jeff Moden Example
--Now, before we get to the solution, we need to make a well indexed table of sequential numbers. These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000 as if we were using ROWNUM from SQL Server 2005. You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Now find the unused bar codes it's just a left join, no big deal
SELECT Tally.N as AvailableBarCode from Tally
left outer join ExistingBarCodes on Tally.N = ExistingBarCodes.Barcode
WHERE ExistingBarCodes.Barcode IS NULL
--===== only numbers between a range?
SELECT Tally.N as AvailableBarCode from Tally
left outer join ExistingBarCodes on Tally.N = ExistingBarCodes.Barcode
WHERE ExistingBarCodes.Barcode IS NULL
AND Tally.N between 101 and 125
Results:
AvailableBarCode
----------------
106
107
108
109
114
115
116
117
118
119
122
123
124
125
Lowell
September 27, 2007 at 12:22 am
Heh... thanks, Lowell... you're too kind :blush:
Clarence,
If your barcodes/customer numbers get above 11,000, you can very easily change the "Tally" table to a million, but I wouldn't go much higher than that... write back if you need a larger range than a million... I've got a nasty fast "find missing ID's" script that gives the missing numbers as ranges rather than individual numbers....
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2007 at 7:07 am
Thanks Lowell and Jeff. Sorry for not getting back sooner but it's been hectic around here! Jeff your routine works perfectly at finding the gaps in the barcode sequences. I only need up to 99999 which should be enough for 3 years worth of assigning/reassigning the barcodes.
Now that Jeff has provided the method for finding the gaps, though, and please bear with a newbie, where would I place the code to read through and assign the barcodes to the new records? I tried a couple of places and ended up assigning *every* record the first available barcode that Jeff's routine found. That was scary :w00t: !
Thanks again for all your assistance!
Clarence VanDyke
School-Link Technologies, Inc.
September 29, 2007 at 9:44 am
Are you just wanting to grab the first unused barcode each time a new customer is generated or processed? Then use Jeff's/Lowell's statement with Min() on the insert/update.
SELECT MIN(Tally.N) as AvailableBarCode from Tally
left outer join ExistingBarCodes on Tally.N = ExistingBarCodes.Barcode
WHERE ExistingBarCodes.Barcode IS NULL
Your last post indicates that maybe you are trying to do a mass update of existing customers that have null barcodes. If this is the case, then something based on the following might work.
I do not have the ability to test this at the moment, but I think it should work. In SQL Server 2000, you might create 2 temp tables with identity fields.
In the first, insert the unused barcodes from the above scripts, and in the second, insert the customer's primary key field(s) where barcode is null.
Something like the following would then work.
UPDATE cs SET barcode = av.AvailableBarcode
FROM Customers cs
JOIN #UnassignedCustomers uc
ON cs.PrimaryKey=uc.PrimaryKey
JOIN #AvailableBarcodes av
ON uc.IdentityField=av.IdentityField
If you are using SQL 2005, it should be even easier using the row_number function, but I have not had enough experience using it to tell you how without being in a position to test my code.
Good luck.
Donnie
September 29, 2007 at 11:13 pm
I gotta ask, because you're a newbie....
Why are you worried about reassigning barcodes? Bar codes can be made to easily exceed 2 billion (approximate max for an INT). Why not just issue new ones all the time. Would make life lot's simpler and allow traceability for all customers throughout history...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2007 at 7:53 am
The reason I need to reassign bar codes is it's what the customer wants. It is a school district who is assigning barcodes to students and they don't want the numbers to be greater than 5 digits and they want to backfill so that there is no "empty spots" in their numbering system, believe me if the final decision was up to me I would just let the barcodes continue an upward numbering system without worrying about recycling numbers. When a student transfers from one school to another they want to assign a barcode "within that schools range". Each school has it's own "range" of numbers. Elementary School A has barcodes within the range of 1000 - 2000 and Elementary School B has barcodes within the range of 2001-3000 and High School A has barcodes within the range of 3001 - 5000 and so on for each school within that District's jurisdiction.
Thanks for all the good replies and assistance.
Clarence VanDyke
October 11, 2007 at 11:34 am
Jeff Moden (9/27/2007)
... I've got a nasty fast "find missing ID's" script that gives the missing numbers as ranges rather than individual numbers....
Jeff, I kind of remember that one from Belution. Would you mind? Coincidentally I needed to do something similar with address ranges last week. By the way, is Belution kaput?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 11, 2007 at 6:09 pm
Hi Greg,
I don't know if Belution is kaput or not... I've seen them go down for several weeks only to rise again. Looks pretty bad this time, though... haven't seen it down this long before.
Anyway, here's the "find missing numbers in a sequence code"... I'm not the original author but I can't remember the name of the guy that posted it...
Here's the data I use to test it with...
--===== Setup for speed and to prevent blocking
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--=============================================================================
-- Create an experimental table to simulate the table being examined
--=============================================================================
--===== If the experimental temp table exists, drop it so we can do repeat runs
-- if we want to "play".
IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL
DROP TABLE #yourtable
--===== Create the experimental temp table and populate with IDs on the fly
-- This always works because Master.dbo.SysColumns always has at least 4000 entries
-- even in a new database and 4000*4000 > 1,000,000
SELECT TOP 1000000 ID = IDENTITY(INT, 1, 1)
INTO #yourtable
FROM Master.dbo.SYSCOLUMNS sc1,
Master.dbo.SYSCOLUMNS sc2
--===== Like any good table, our experimental table needs a Primary Key
ALTER TABLE #yourtable
ADD PRIMARY KEY CLUSTERED (ID)
--===== Ok, we have an experimental table with a million IDs from 1 to 1,000,000.
-- Let's remove some of the rows and see if we can find if they're missing
-- This deletes a handful of individual rows and 3 very small ranges
DELETE #yourtable
WHERE ID IN (2,3,250,251,2000,4000,4002,4004,900001,900002,900003,999999)
-- This deletes a "monster" range just to see how it's handled.
DELETE #yourtable
WHERE ID BETWEEN 500000 AND 700000
OR ( ID BETWEEN 700000 AND 800000
AND ID%2 = 0)
... and here's the actual routine that finds all missing ranges of IDs. Obviously, if the MinRange = the MaxRange, then it's a single value missing. Takes about 2 seconds on a million rows to find ALL of the ranges...
--===== This short little ditty is what does the actual work
SELECT 'List of missing ranges of ID''s'
SELECT MinRange = (SELECT ISNULL(MAX(suba.ID),0)+1
FROM #yourtable suba
WHERE suba.ID < a.ID),
MaxRange = ID - 1
FROM #yourtable a
WHERE a.ID - 1 NOT IN (SELECT ID FROM #yourtable)
AND a.ID - 1 > 0
... throw in a TOP 1 and it will find the lowest range in less than a blink...
--===== This short little ditty is what does the actual work
SELECT 'Lowest missing ID range'
SELECT TOP 1
MinRange = (SELECT ISNULL(MAX(suba.ID),0)+1
FROM #yourtable suba
WHERE suba.ID < a.ID),
MaxRange = ID - 1
FROM #yourtable a
WHERE a.ID - 1 NOT IN (SELECT ID FROM #yourtable)
AND a.ID - 1 > 0
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 8:53 am
Thanks Jeff, that's good stuff there.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 12, 2007 at 6:00 pm
You bet... thanks for the feedback, Greg.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply