Identifying gaps in numbering sequence of a table
-
All
I am trying to identify gaps in numbering sequence of a table used for our vendors. This is due to a removal of redundant data after a takeover of another company. We received and uploaded the inherited data but some of the vendors were not used, so after 12 months so they were removed from the vendor table.
Can anyone suggest a way to id gaps in the sequence for us to reuse when creasting new vendor?
The customers have prefix v9* (to identify vendors and 9 is company identifier the vendor is for)
Thanks
Dan
-
Hi Dan
Instructions for creating a numbers table exist on the forum, e.g. see posts by Jeff Moden.
Left-join your vendors table against a numbers table and use a WHERE clause, like this:
SELECT 'v9' + CAST(n.number AS whatever...
FROM numbers n LEFT JOIN vendors v
ON v.vendorID = 'v9' + CAST(n.number AS whatever...
WHERE v.vendorID IS NULL
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Bledu
Additional info as requested.
Vendor number made up of letter and 5 numbers, i.e. v90001, v90002, v90003, etc. I have vendor v90001 to v90500.
As explained due to a recent admin exercise, to delete inherited unused vendors, I now have a number of gaps in the range and I would like to identify to reuse for new vendors. So, for example I now have: -
v90001, v90004, v90005, v90006, v90007, v90015, etc.
I would like to produce a query to list the 'free' numbers so I can provide a listing to admin person creating vendors so that they use these number and does create a new vendor as v90501, etc.
-
Sounds wonderfully efficient. HOWEVER - are there records retention issues you may fall afoul of? Will reusing vendor numbers cause confusion down the road, as a policy? Or is this guaranteed to be a one-time event due to the takeover of another company?
Are you worried about running out of available values, or is it that you would like to fill in the blanks, and THEN be able to use a MAX(vendor_number) to find the first open value? A coding solution has been proposed for your use, and I suspect that it will work well. But what's the context you will use it in, for inserting another vendor #? What happens when there are more than 500 active vendors?
-
-
-
I wrote the function on the code below to quickly generate number tables.
It executed this code to load a table with 1,000,000 numbers in 6.780 seconds. When I ran it to load a table with 10,000,000 numbers, it took about 136 seconds. 100,000 rows took 0.610 seconds, and 10,000 rows took 0.063 seconds.
declare @t datetime declare @n table( number int ) set @t = getdate() insert into @n
select number from -- Function F_TABLE_NUMBER_RANGE available on this link -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 F_TABLE_NUMBER_RANGE(1,1000000)
select ElapsedTime = getdate()-@t
-
As much as I love number (Tally) tables, there's absolutely no need for one here... nore is there the need for a bunch of functions, etc. I don't remember where the heck I got this from, but I've never found anything faster for finding gaps in a Sequence. Of course, I've included a pot-wad full of test data... the actual solution is the last SELECT in this whole ball of wax...
--===== 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
-- THIS IS NOT PART OF THE SOLUTION. IT'S JUST A TEST SETUP.
--=============================================================================
--===== 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
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)
--=============================================================================
-- THIS IS THE PART WHERE THE SOLUTION IS. YOU'D NEED TO MAKE A SMALL
-- MOD TO GET IT TO HANDLE THE "V" PREFIX
--=============================================================================
--===== This short little ditty is what does the actual work
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
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
P.S. I'll kindly with-hold the lecture about making such mixed character sequences
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
All
Thanks for the help! These posts helped me solve this.
Dan
-
Thats nice Dan... wanna share your solution? Information does travel in two directions...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply