Posted by Thomas R. Hummel on 10/07/05 17:23

I've come up with a solution to the problem which I thought might be
useful for others. My problem was that I kept trying to find different
ways to force the data into the same structure. Finally it occurred to
me to look for a better model that is easier to work with.

I came up with the following:

CREATE TABLE Chain (
My_ID INT NOT NULL,
Orig_ID INT NOT NULL,
Level INT NOT NULL)
GO
ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
GO

Now, converting the data is fairly quick and simple. First I add in all
of the "base" claims. Those are any claims that aren't copied from
somewhere else:

INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT CT1.My_ID, CT1.My_ID, 0
FROM Copy_To CT1
LEFT OUTER JOIN Copy_To CT2 ON CT2.Copy_To = CT1.My_ID
WHERE CT2.My_ID IS NULL

Once the base claims are added I add each new level in a loop until
there are no levels left:

DECLARE @ins_count INT
SET @ins_count = 1
WHILE (@ins_count > 0)
BEGIN
INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT
MIN(CF.My_ID),
C1.Orig_ID,
SQ.Max_Level + 1
FROM Copy_From CF
INNER JOIN Chain C1 ON C1.Orig_ID = CF.Copy_From
INNER JOIN (SELECT Orig_ID, MAX(Level) AS Max_Level
FROM Chain
GROUP BY Orig_ID) SQ ON SQ.Orig_ID = C1.Orig_ID
LEFT OUTER JOIN Chain C2 ON C2.My_ID = CF.My_ID
WHERE C2.My_ID IS NULL
GROUP BY C1.Orig_ID, SQ.Max_Level

SELECT @ins_count = @@ROWCOUNT
END

This logic handles all of the various problems with the data such as a
single claim being copied to multiple destination claims. The one
problem that it doesn't handle is a claim which is copied FROM multiple
claims, but this is an unresolvable error condition, so I find those
rows and move them to an error table ahead of time.

To improve performance I made a unique clustered index for Chain on the
Orig_ID and Level.

Getting the data that I need is fast and easy from this table as well:

SELECT C.My_ID, C.Orig_ID, CF.My_ID AS Copy_From, CT.My_ID AS Copy_To
FROM Chain C
LEFT OUTER JOIN Chain CF ON CF.Orig_ID = C.Orig_ID AND CF.Level =
C.Level - 1
LEFT OUTER JOIN Chain CT ON CT.Orig_ID = C.Orig_ID AND CT.Level =
C.Level + 1

-Tom.

[Back to original message]