CREATE TABLE [dbo].[interval](
[id] [bigint] NOT NULL,
[start_date] [date] NOT NULL,
[end_date] [date] NOT NULL,
[name] [nvarchar](50) NOT NULL
);
GO
insert interval values (1,'2010-05-05','2010-12-12','aaa');
insert interval values (2,'2011-05-05','2011-12-12','aaa');
insert interval values (3,'2008-01-01','2009-05-05','aaa');
insert interval values (4,'2010-05-05','2010-12-12','bbb');
GO
DECLARE @delta TINYINT = 10;
DECLARE @Rezultate TABLE (ID1 BIGINT NOT NULL, ID2 BIGINT NOT NULL, GroupID INT IDENTITY(1,1));
INSERT @Rezultate(ID1, ID2)
SELECT I.id AS ID1
,II.id AS ID2
FROM interval I
JOIN interval II ON I.id <> II.id
AND I.name = II.name
AND I.end_date <= II.start_date
AND II.start_date <= DATEADD(MM, @delta, I.end_date)
SELECT i.*
,r.GroupID
FROM @Rezultate r
JOIN interval i ON r.ID1 = i.id
UNION ALL
SELECT i.*
,r.GroupID
FROM @Rezultate r
JOIN interval i ON r.ID2 = i.id
UNION ALL
SELECT i.*
,(SELECT COUNT(*) FROM @Rezultate) + ROW_NUMBER()OVER(ORDER BY i.id) AS GroupID
FROM interval i
WHERE i.id NOT IN (SELECT r.ID1 FROM @Rezultate r)
AND i.id NOT IN (SELECT r.ID2 FROM @Rezultate r)
Este posibil ca modul în care sunt generate valorile GroupID să necesite modificări deoarece
în lipsa unor precizări am presupus că fiecare grup poate avea maxim două înregistrări.