SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find First Missing Value Sequence
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mkdlmr
Starting Member

21 Posts

Posted - 02/22/2014 :  13:30:40  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 02/22/2014 :  14:42:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

mkdlmr
Starting Member

21 Posts

Posted - 02/22/2014 :  15:59:56  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/23/2014 :  03:05:52  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 02/24/2014 :  12:31:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 02/24/2014 :  14:31:42  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 02/25/2014 :  11:33:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30281 Posts

Posted - 02/25/2014 :  11:41:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 02/25/2014 :  12:26:56  Show Profile  Reply with Quote
Thank You!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/25/2014 :  12:31:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000