with rawdata as(select '20090503' as dateid ,7320 as CustID ,'370090510' as CCNumberunion select '20090420' as dateid ,25861 as CustID ,'370090510' as CCNumberunion select '20100404' as dateid ,7320 as CustID ,'370080987' as CCNumberunion select '20090703' as dateid ,25861 as CustID ,'370080987' as CCNumberunion select '20100603' as dateid ,7320 as CustID ,'594220191' as CCNumberunion select '20100705' as dateid ,25861 as CustID ,'594220191' as CCNumberunion select '20100719' as dateid ,7320 as CustID ,'594220654' as CCNumberunion select '20100720' as dateid ,7321 as CustID ,'594220654' as CCNumber)
select '20090503' as dateid ,7320 as CustID ,'370090510' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20090420' as dateid ,25861 as CustID ,'370090510' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Name union select '20100404' as dateid ,7320 as CustID ,'370080987' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20090703' as dateid ,25861 as CustID ,'370080987' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100603' as dateid ,7320 as CustID ,'594220191' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100705' as dateid ,25861 as CustID ,'594220191' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100719' as dateid ,7320 as CustID ,'594220654' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100720' as dateid ,7321 as CustID ,'594220654' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Name
with rawdata as(select '20090503' as dateid ,7320 as CustID ,'370090510' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20090420' as dateid ,25861 as CustID ,'370090510' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Name union select '20100404' as dateid ,7320 as CustID ,'370080987' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20090703' as dateid ,25861 as CustID ,'370080987' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100603' as dateid ,7320 as CustID ,'594220191' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100705' as dateid ,25861 as CustID ,'594220191' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100719' as dateid ,7320 as CustID ,'594220654' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameunion select '20100720' as dateid ,7321 as CustID ,'594220654' as CCNumber ,'Jane' as First_Name ,'Smith' as Last_Nameorder by custid),rawdata2 as(select custid,MAX(dateid) as maxdateidfrom rawdatagroup by custid)SELECT distinct CASE WHEN CustDate1 > CustDate2 THEN CustID1 ELSE CustID2 END AS [GrandMasterCustomer] ,CASE WHEN CustDate1 > CustDate2 THEN CustDate1 ELSE CustDate2 END AS [GrandMasterDate] ,CASE WHEN CustDate1 > CustDate2 THEN CustID2 ELSE CustID1 END AS [SubordinateCustomer]FROM(select r1.CustID as CustID1 , (SELECT MAX(d1.dateid) FROM rawdata AS d1 WHERE d1.CustID = r1.CustID) AS CustDate1 ,r2.CustID as CustID2 , (SELECT MAX(d2.dateid) FROM rawdata AS d2 WHERE d2.CustID = r2.CustID) AS CustDate2 from rawdata r1 join rawdata r2 on r1.CCNumber = r2.CCNumber where r1.CustID<>r2.CustID) AS a
IF OBJECT_ID( 'tempdb..#rawdata') IS NOT NULL DROP TABLE #rawdataIF OBJECT_ID( 'tempdb..#WorkingSet') IS NOT NULL DROP TABLE #WorkingSetCREATE TABLE #rawdata (dateid DATETIME, CustID INT, CCNumber CHAR(9), First_Name VARCHAR(10), Last_Name VARCHAR(10)) INSERT INTO #rawdata (dateid, CustID, CCNumber, First_Name, Last_Name) SELECT '20090503', 7320, '370090510', 'Jane', 'Smith' union SELECT '20090420', 25861, '370090510', 'Jane', 'Smith' union SELECT '20100404', 7320, '370080987', 'Jane', 'Smith' union SELECT '20090703', 25861, '370080987', 'Jane', 'Smith' union SELECT '20100603', 7320, '594220191', 'Jane', 'Smith' union SELECT '20100705', 25861, '594220191', 'Jane', 'Smith' union SELECT '20100719', 7320, '594220654', 'Jane', 'Smith' union SELECT '20100720', 7321, '594220654', 'Jane', 'Smith' SELECT DupesetID = CAST(NULL AS BIGINT), r1.*, r2_CustID = r2.CustIDINTO #WorkingSet FROM #rawdata r1INNER JOIN #rawdata r2 ON r2.CCNumber = r1.CCNumber AND r2.CustID <> r1.CustID ORDER BY r1.CustID-- assign dupesetIDUPDATE d SET DupesetID = s.DupesetIDFROM #WorkingSet d INNER JOIN ( SELECT DupesetID = ROW_NUMBER() OVER (ORDER BY Last_Name, CCNumber), Last_Name, CCNumber FROM #WorkingSet GROUP BY Last_Name, CCNumber) s ON s.Last_Name = d.Last_Name AND s.CCNumber = d.CCNumber-- merge sets with same customerID to earliest DupesetIDUPDATE d SET DupesetID = s.DupesetIDFROM #WorkingSet dINNER JOIN ( SELECT CustID, DupesetID = MIN(DupesetID), OLDDupesetID = MAX(DupesetID) FROM #WorkingSet GROUP BY CustID HAVING COUNT(*) > 1 AND MIN(DupesetID) <> MAX(DupesetID)) s ON s.OLDDupesetID = d.DupesetID-- merge again if necessary-- assign principal, and number of rows in dupesetSELECT MasterCustomer = ROW_NUMBER() OVER (PARTITION BY d.DupesetID ORDER BY dateid DESC), DupesetSize = COUNT(*) over (PARTITION BY d.DupesetID), d.DupesetID, d.dateid, d.CustID, d.CCNumber, d.First_Name, d.Last_Name, d.r2_CustID FROM #WorkingSet d ORDER BY DupesetID, MasterCustomer