Sequential Numbers Joined to Serial Number Table

Question

Sequential Numbers Joined to Serial Number Table

Tek

SSCommitted

Points: 1697
More actions
July 12, 2016 at 1:46 pm

#315015

Can someone assist me with some coding. So I'm creating a report that displays available serial numbers. These serial numbers are not known until the clerks get a batched of serial numbered paper. I do have the serial numbers that have been used, so I have a starting point.
What I'm trying to do is based off the serial numbers that have been used, which I know what these numbers are, I want a result that displays what's not been used.
So if my database has serial numbers 1,2,3,4,5,7,10,..... Of course, my true serial numbers doesnt start out that way, just for example.
I want to display the missing numbers, such as 6, 8, 9, and so on.
There's no end to these serial numbers, but I understand we cant continue on into infinity.
So here's what I have so far. Hopefully someone can help to make this more efficient or maybe a different method. Thanks in advance.
DECLARE @START_SN INT = 13324 --keep for report
DECLARE @END_SN INT = 32000 --keep for report
; WITH SEQ AS
(
SELECT @START_SN AS 'SEQNO'
UNION ALL
SELECT SEQNO + 1
FROM SEQ
WHERE SEQNO + 1 <= @END_SN
)
, SN AS (
SELECT SERIAL_NUMBER
FROM TABLE_NAME
WHERE SERIAL_NUMBER IS NOT NULL
)
SELECT *
FROM SEQ
WHERE SEQNO NOT IN (SELECT SERIAL_NUMBER FROM SN)
OPTION (MAXRECURSION 32000)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There's a problem with your sequential number generator. It's explained in here: http://www.sqlservercentral.com/articles/T-SQL/74118/

Here are 2 alternatives. One is using NOT IN as in your original query, the other is using EXCEPT which is usually faster. Both use a very effective way to generate a numbers table.

CREATE TABLE #SampleData( serial_Number int)

INSERT INTO #SampleData VALUES(1),(2),(3),(4),(5),(7),(10)

DECLARE @START_SN INT = 1 --keep for report

DECLARE @END_SN INT = 15; --keep for report

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT TOP(@END_SN - @START_SN + 1)

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + @START_SN

FROM E4

)

SELECT n

FROM cteTally

WHERE n NOT IN (SELECT SERIAL_NUMBER

FROM #SampleData

WHERE SERIAL_NUMBER IS NOT NULL);

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT TOP(@END_SN - @START_SN + 1)

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + @START_SN

FROM E4

)

SELECT n

FROM cteTally

EXCEPT

SELECT SERIAL_NUMBER

FROM #SampleData

WHERE SERIAL_NUMBER IS NOT NULL;

GO

DROP TABLE #SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Tek SSCommitted Points: 1697 More actions · Answer 2

Luis Cazares (7/12/2016)
There's a problem with your sequential number generator. It's explained in here: http://www.sqlservercentral.com/articles/T-SQL/74118/
Here are 2 alternatives. One is using NOT IN as in your original query, the other is using EXCEPT which is usually faster. Both use a very effective way to generate a numbers table.
CREATE TABLE #SampleData( serial_Number int)
INSERT INTO #SampleData VALUES(1),(2),(3),(4),(5),(7),(10)
DECLARE @START_SN INT = 1 --keep for report
DECLARE @END_SN INT = 15; --keep for report
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(@END_SN - @START_SN + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + @START_SN
FROM E4
)
SELECT n
FROM cteTally
WHERE n NOT IN (SELECT SERIAL_NUMBER
FROM #SampleData
WHERE SERIAL_NUMBER IS NOT NULL);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(@END_SN - @START_SN + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + @START_SN
FROM E4
)
SELECT n
FROM cteTally
EXCEPT
SELECT SERIAL_NUMBER
FROM #SampleData
WHERE SERIAL_NUMBER IS NOT NULL;
GO
DROP TABLE #SampleData

This works out perfectly. Thanks!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

DarthBurrito (7/12/2016)
This works out perfectly. Thanks!

Since you're the one that will have to maintain it, do you understand it... perfectly?

--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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Alternatively you can do it like this:

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT TOP(@END_SN - @START_SN + 1)

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + @START_SN

FROM E4

)

SELECT n

FROM cteTally t

LEFT JOIN #SampleData sd

ON serial_Number = n

WHERE serial_Number IS NULL;

This works like Luis' solution that uses EXCEPT but does not create a Distinct sort operation in the execution plan.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001