"Jason" <NO*********@msn.com> wrote in message news:52******************@nwrddc01.gnilink.net...
What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.
For instance say I have the following data in table:
Account Name
--------- --------
50100 Test1
50105 Test2
50106 Test4
.
.
.
I should see the results:
50101
50102
50103
50104
because 50101-50104 are available account numbers since not currently in
table.
Thanks.
Here's a UDF that will fill in all missing integers between a
lower bound and an upper bound, both inclusive. By default,
the lower bound is 50100 and the upper bound is 70100.
CREATE TABLE Accounts
(
account_id INT NOT NULL PRIMARY KEY,
account_name VARCHAR(10) NOT NULL
)
CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)
INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)
INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d + 10000 * TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CREATE FUNCTION IntervalGaps
(@lower INT = 50100, @upper INT = 70100)
RETURNS @missing TABLE (n INT NOT NULL PRIMARY KEY)
AS
BEGIN
IF @lower > @upper
RETURN
INSERT INTO @missing (n)
SELECT @lower + I.n
FROM (SELECT MIN(account_id)
FROM Accounts) AS A(least_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A.least_id - @lower
INSERT INTO @missing (n)
SELECT A1.account_id + I.n + 1
FROM Accounts AS A1
INNER JOIN
Accounts AS A2
ON A2.account_id > A1.account_id + 1 AND
NOT EXISTS (SELECT *
FROM Accounts
WHERE account_id > A1.account_id AND
account_id < A2.account_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A2.account_id - A1.account_id - 1
INSERT INTO @missing (n)
SELECT A.greatest_id + I.n + 1
FROM (SELECT MAX(account_id)
FROM Accounts) AS A(greatest_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < @upper - A.greatest_id
RETURN
END
-- Sample data
INSERT INTO Accounts (account_id, account_name)
SELECT 50102, 'test1'
UNION ALL
SELECT 50105, 'test2'
UNION ALL
SELECT 50106, 'test4'
SELECT n AS account_id
FROM IntervalGaps(DEFAULT, DEFAULT)
account_id
50100
50101
50103
50104
50107
50108
....
70100
Regards,
jag