We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID.
There are three different types of cases that need to be solved.
- Simple case of A->B->C. In the view, all three of these records would receive the same
GroupID
- Two or more
DefectID
s come from the samePreviousDefectID
. In this case, the view would have a differentGroupID
for each new branch that is created. This can keep happening in more than one generation. - A
DefectID
has two or morePreviousDefectID
s (comma seprated)
So, the following table:
CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))
INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
('B', 'A'),
('C', 'B'),
('D', NULL),
('E', 'D'),
('F', 'D'),
('G', 'D'),
('H', 'E'),
('I', 'E'),
('K', NULL),
('L', NULL),
('M', NULL),
('N', 'L,M'),
('O', 'K,N')
Would result in a view that looks like the following table:
CREATE TABLE #Final (
GroupID INT,
ID VARCHAR(50),
PreviousID VARCHAR(50))
INSERT INTO #Final (GroupID, ID, PreviousID)
VALUES (1, 'A', NULL),
(1, 'B', 'A'),
(1, 'C', 'B'),
(2, 'D', NULL),
(3, 'D', NULL),
(4, 'D', NULL),
(2, 'E', 'D'),
(3, 'F', 'D'),
(4, 'G', 'D'),
(7, 'D', NULL),
(7, 'E', 'D'),
(7, 'H', 'E'),
(7, 'I', 'E'),
(5, 'K', NULL),
(5, 'L', NULL),
(5, 'M', NULL),
(5, 'N', 'L,M'),
(5, 'O', 'K,N')
Note, there are more records in the resulting view than in the table, because when a single DefectID
leads to multiple other DefectID
s, there is a new branch created for each chain and each gets their own GroupID
number.
I know this is a complicated question, any help at all would be appreciated.
I am attaching what I have so far, but it isn't quite correct, and doesn't yet address the third type of situation.
select ID,
PreviousID, count(PreviousID) over (partition by ID, PreviousID) as 'cnt', null
from #temp temp
union all
(
select temp.ID, temp.PreviousID, null, row_number () over (partition by temp.ID, temp.PreviousID order by temp.PreviousID) as 'rn'
from #temp temp inner join #temp temp1 on temp.ID = temp1.PreviousID
)
order by 1