Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find First Missing Value Sequence

Author  Topic 

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-22 : 13:30:40
Hi All,

I have a alphanumeric column that looks something like this:

T0001
T0003
T0004
T0005

How would I go about identifying T0002 as the missing value in the sequence?

Thanks,
MK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-22 : 14:42:05
[code]DECLARE @Sample TABLE
(
Data CHAR(5) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('T0001'),
('T0003'),
('T0004'),
('T0005');

WITH cteSource(Data, rn, info)
AS (
SELECT Data,
ROW_NUMBER() OVER (ORDER BY Data) AS rn,
CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info
FROM @Sample
)
SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]
FROM cteSource
WHERE rn <> Info
ORDER BY rn;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-22 : 15:59:56
Thanks! That seems to do the trick, but I did notice that it cannot pick up a missing lead number. For example:

T0002
T0003
T0004
T0005

Will not return T0001. Also, is there a way to return the next result if it finds nothing? IE:

T0001
T0002
T0003

would return T0004 as the next number? I hope I'm not asking for too much. O:-)

Thanks,
MK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-23 : 03:05:52
quote:
Originally posted by mkdlmr

Thanks! That seems to do the trick, but I did notice that it cannot pick up a missing lead number. For example:

T0002
T0003
T0004
T0005

Will not return T0001. Also, is there a way to return the next result if it finds nothing? IE:

T0001
T0002
T0003

would return T0004 as the next number? I hope I'm not asking for too much. O:-)

Thanks,
MK


the easiest way to implement this is by using a calculated column based on an IDENTITY field
see
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-24 : 12:31:50
quote:
Originally posted by mkdlmr

Will not return T0001.
Did you even TRY my suggestion?
DECLARE	@Sample TABLE
(
Data CHAR(5) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('T0002'),
('T0003'),
('T0004'),
('T0005');

WITH cteSource(Data, rn, info)
AS (
SELECT Data,
ROW_NUMBER() OVER (ORDER BY Data) AS rn,
CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info
FROM @Sample
)
SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]
FROM cteSource
WHERE rn <> Info
ORDER BY rn;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-24 : 14:31:42
Yes, I did try it out, but it did not start at 1 if 1 was missing or increment after the last one.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:33:09
That's because the original sample data only reflected the first requirement.
If you change the sample data (just the way I did in my second post), you automatically get your solution as per second requirement too.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:41:51
Now, look at the magic. The only thing I do is changing the sample data.
The actual work being done is intact between test cases.
-- First test case (second value missing: answer is T0002)
DECLARE @Sample TABLE
(
Data CHAR(5) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('T0001'),
('T0003'),
('T0004'),
('T0005');

WITH cteSource(Data, rn, info)
AS (
SELECT Data,
ROW_NUMBER() OVER (ORDER BY Data) AS rn,
CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info
FROM (
SELECT Data
FROM @Sample

UNION

SELECT 'Z9999'
) AS d
)
SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]
FROM cteSource
WHERE rn <> Info
ORDER BY rn;
GO
-- Second test case (first value missing: answer is T0001)
DECLARE @Sample TABLE
(
Data CHAR(5) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('T0002'),
('T0003'),
('T0004'),
('T0005');

WITH cteSource(Data, rn, info)
AS (
SELECT Data,
ROW_NUMBER() OVER (ORDER BY Data) AS rn,
CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info
FROM (
SELECT Data
FROM @Sample

UNION

SELECT 'Z9999'
) AS d
)
SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]
FROM cteSource
WHERE rn <> Info
ORDER BY rn;
GO
-- Third test case (last value missing: answer is T0005)
DECLARE @Sample TABLE
(
Data CHAR(5) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('T0001'),
('T0002'),
('T0003'),
('T0004');

WITH cteSource(Data, rn, info)
AS (
SELECT Data,
ROW_NUMBER() OVER (ORDER BY Data) AS rn,
CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info
FROM (
SELECT Data
FROM @Sample

UNION

SELECT 'Z9999'
) AS d
)
SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]
FROM cteSource
WHERE rn <> Info
ORDER BY rn;
GO
Yes, you can say "Thank you" now.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-25 : 12:26:56
Thank You!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 12:31:18
You're welcome.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -