--Try this:
/* Assumption: Overlap is defined by two or more tempid's having the same logID, not that the ranges of their log id's overlap
Overlap is communitive
For example:
TempID 2 has logids 6 and 9
TempID 3 has logids 7 and 8
TempID 4 has logids 6 and 12
TempID 5 has logids 12 and 15
TempID 6 has logids 8 and 18
TempId 7 has logids 20 and 21
Result
TempID 2 overlaps with 4 (share 6) and with 5 (since 4 and 5 share 12) but not with 3 even though 3's logids are inside of the range of temp2 id's
TempID 3 overlaps with 6 (share 8)
TempID 7 does not overlap with anything
*/
/* Algorithm
Find which TempID's overlap and Assign them a common ID. Example result set:
2 2
5 2
3 2
7 7
8 7
Find the lowest LogID of the overlaps
Update the AltLogID
*/
USE TEMPDB
GO
CREATE TABLE #tempIDs (TempID int, LogID int, AltLogID int)
GO
INSERT #tempIDs VALUES(2, 10, 10)
INSERT #tempIDs VALUES(2 , 12 , 12)
INSERT #tempIDs VALUES(2 , 15 , 15)
INSERT #tempIDs VALUES(3, 15 , 15)
INSERT #tempIDs VALUES(3 , 17 ,17)
INSERT #tempIDs VALUES(3 , 20, 20)
INSERT #tempIDs VALUES(5 , 16 , 16)
INSERT #tempIDs VALUES(5, 17 , 17)
INSERT #tempIDs VALUES(5 , 30 , 30)
INSERT #tempIDs VALUES(6 , 25 ,25)
INSERT #tempIDs VALUES(6 , 27 ,27)
INSERT #tempIDs VALUES(7 , 32 ,32)
INSERT #tempIDs VALUES(7 , 8 ,8)
INSERT #tempIDs VALUES(8 , 32 ,32)
INSERT #tempIDs VALUES(8 , 56 ,56)
GO
WITH TJ (T1TempID, T1LogID, T1AltLogID, T2TempID, T2LogID, T2AltLogID) -- Brings back the first level of matches
AS
(
SELECT T1.TempID, T1.LogID, T1.AltLogID, T2.TempID, T2.LogID, T2.AltLogID
FROM #tempIDs T1
JOIN #tempIDs T2
ON T1.LogID = T2.LogID
AND T1.TempID < T2.TempID
)
, TIDS (T1TempID, T1LogID, T1AltLogID, TT1, TLevel) -- Recursive CTE -- Flushes out all of the possible matches
-- so that if 2-3 and 3-5 then 2-5 and 5-2 will also show up
AS
( -- First Anchor Member -- will return rows that won't have any recursion
SELECT T1.TempID, T1.LogID, T1.AltLogID, T1.TempID, 0 AS TLevel
FROM #tempIDs T1
WHERE T1.TempID NOT IN
(SELECT T1TempID
FROM TJ
UNION
SELECT T2TempID
FROM TJ
)
UNION ALL--Second Anchor Member -- will return rows that will recurse
SELECT T1TempID, T1LogID, T1AltLogID, T1TempID as TT1, 0 AS TLevel
FROM TJ
UNION ALL -- Recursive Member Query
SELECT T2TempID, T2LogID, T2AltLogID, TIDS.TT1, TLevel + 1 AS TLevel
FROM TJ
JOIN TIDS
ON TIDS.T1TempID = TJ.T1TempID
AND TJ.T2TempID != TIDS.T1TempID
)
, MINTIDS (T1TempID, MINTT1) -- pares it down to use the lowest tempid as a common identifier among all of the overlapping
AS
(
SELECT T1TempID, MIN(TT1) FROM TIDS
GROUP BY T1TempID
)
, MINLOGIDS (MINTT1, MINLogID) -- Find out what is the lowest LogID
AS
(
SELECT MT.MINTT1, MIN(T1.LogID)
FROM MINTIDS MT
JOIN #tempIDs T1
ON T1.TempID = MT.T1TempID
GROUP BY MT.MINTT1
)
--SELECT MT.T1TempID, ML.MINLogID -- Show the LowestLogID, next to each of the TempID's
--FROM MINLOGIDS ML
--JOIN MINTIDS MT
-- ON ML.MINTT1 = MT.MINTT1
UPDATE #tempIDs SET AltLogID = ML.MINLogID
FROM #tempIDs T
JOIN MINTIDS MT
ON T.TempID = MT.T1TempID
JOIN MINLOGIDS ML
ON ML.MINTT1 = MT.MINTT1
SELECT * FROM #tempIDs
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com