Serial Numbers
There are typically two systems for assigning serial numbers to parts on a line.
- The serial number is assigned at the beginning of the line (process) and usually engraved at that point. In a case of rework (reprocessing) the same serial number is scanned as the part re-enters the process.
- In the second case, only good parts are engraved just before unloading, and all other are issued a "sticker" with a serial number. If a part re-enters the line, a new number is issued and the previous one is scanned.
In the second case it is a bit harder to find (merge) all data for a part which re-entered the line several times. The algorithm for it is similar to classic family tree navigation. To simplify the explanation, create the following table with some data and look at the result of the select statement.
CREATE TABLE #People(
SSN BIGINT PRIMARY KEY, -- social security number
ParentSSN BIGINT, -- parent's social security No.
[Name] VARCHAR(25) -- person's name
);
--
/* Insert some data */
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1199, 700, 'Doug');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1105, 1101, 'Sandy');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1234, 1100, 'Tamara');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1001, 600, 'Don');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1235, 1177, 'John');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1100, 1001, 'Deb');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1272, 995, 'Sean');
SSN BIGINT PRIMARY KEY, -- social security number
ParentSSN BIGINT, -- parent's social security No.
[Name] VARCHAR(25) -- person's name
);
--
/* Insert some data */
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1199, 700, 'Doug');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1105, 1101, 'Sandy');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1234, 1100, 'Tamara');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1001, 600, 'Don');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1235, 1177, 'John');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1100, 1001, 'Deb');
INSERT INTO #People (SSN, ParentSSN, [Name])
VALUES (1272, 995, 'Sean');
Some of these people are related, some not. To find all ancestors of a person, use the following:
CREATE TABLE #tmp(
NM VARCHAR(25), -- person's name
SN BIGINT PRIMARY KEY, -- person SSN
PA BIGINT, -- parent SSN
GE INT -- generation
);
--
DECLARE
@Gen INT,
@LookFor BIGINT;
--
/* The person's SSN to start with */
SET @LookFor = 1234;
--
/* Insert the first person, generation 0 */
SET @Gen = 0;
INSERT INTO #tmp(SN, PA, GE, NM)
SELECT [SSN], [ParentSSN], @Gen, [Name]
FROM #People
WHERE [SSN] = @LookFor;
--
WHILE @@ROWCOUNT > 0
BEGIN
SET @Gen = @Gen + 1;
INSERT INTO #tmp(SN, PA, GE, NM)
SELECT [SSN], [ParentSSN], @Gen, [Name]
FROM #People
JOIN #tmp
ON #tmp.[GE] = @Gen - 1
AND #tmp.[PA] = [SSN]
END;
--
SELECT
[NM] AS "Name",
[SN] AS "SSN",
[PA] AS "Parent SSN",
[GE] AS "Generation"
FROM #tmp ORDER BY [GE] ASC;
--
DROP TABLE #tmp;
DROP TABLE #People;
NM VARCHAR(25), -- person's name
SN BIGINT PRIMARY KEY, -- person SSN
PA BIGINT, -- parent SSN
GE INT -- generation
);
--
DECLARE
@Gen INT,
@LookFor BIGINT;
--
/* The person's SSN to start with */
SET @LookFor = 1234;
--
/* Insert the first person, generation 0 */
SET @Gen = 0;
INSERT INTO #tmp(SN, PA, GE, NM)
SELECT [SSN], [ParentSSN], @Gen, [Name]
FROM #People
WHERE [SSN] = @LookFor;
--
WHILE @@ROWCOUNT > 0
BEGIN
SET @Gen = @Gen + 1;
INSERT INTO #tmp(SN, PA, GE, NM)
SELECT [SSN], [ParentSSN], @Gen, [Name]
FROM #People
JOIN #tmp
ON #tmp.[GE] = @Gen - 1
AND #tmp.[PA] = [SSN]
END;
--
SELECT
[NM] AS "Name",
[SN] AS "SSN",
[PA] AS "Parent SSN",
[GE] AS "Generation"
FROM #tmp ORDER BY [GE] ASC;
--
DROP TABLE #tmp;
DROP TABLE #People;
So what does this have to do with car parts (pumps)?
The search algorithm is the same, substitute:
- SSN = Serial Number,
- ParentSSN = Tracking Number (previous Serial Number),
- ignore the name field
and the same code will find all serial numbers associated with a reworked pump.
