clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 635548 fiddles created (13181 in the last week).

CREATE TABLE #EventTable ( M_ID INT, Y_ID INT, T_ID INT, E_ID INT, BeginDate DATETIME, EndDate DATETIME ); -- INSERT INTO #EventTable VALUES (135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'), (135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'), (135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'), (135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'), (135709, 2, 7, 3, '01 May 2017', '31 May 2017'), (135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'), (135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'), (135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'), (135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'), (135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'), (134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'), (134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'), (134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'), (134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'), (135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'), (135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'), (135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'), (135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'), (123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');
19 rows affected
 hidden batch(es)


select * from #EventTable order by M_ID,Y_ID,T_ID,E_ID,BeginDate ASC;
M_ID Y_ID T_ID E_ID BeginDate EndDate
123457 4 2 3 01/05/2017 00:00:00 31/10/2017 00:00:00
134560 5 8 3 01/04/2017 00:00:00 31/08/2017 00:00:00
134560 5 8 3 01/05/2017 00:00:00 31/08/2017 00:00:00
134560 5 8 3 01/10/2017 00:00:00 31/10/2017 00:00:00
134560 5 8 3 01/11/2017 00:00:00 30/11/2017 00:00:00
135678 3 6 2 01/01/2017 00:00:00 31/03/2017 00:00:00
135678 3 6 2 01/04/2017 00:00:00 30/06/2017 00:00:00
135678 3 6 2 01/10/2017 00:00:00 31/10/2017 00:00:00
135678 3 6 2 01/11/2017 00:00:00 31/12/2017 00:00:00
135709 2 7 1 01/01/2017 00:00:00 31/03/2017 00:00:00
135709 2 7 1 01/04/2017 00:00:00 30/06/2017 00:00:00
135709 2 7 3 01/01/2017 00:00:00 31/01/2017 00:00:00
135709 2 7 3 01/02/2017 00:00:00 30/04/2017 00:00:00
135709 2 7 3 01/05/2017 00:00:00 31/05/2017 00:00:00
135709 2 7 3 01/07/2017 00:00:00 31/08/2017 00:00:00
135709 2 7 3 01/10/2017 00:00:00 31/10/2017 00:00:00
135709 2 7 3 01/12/2017 00:00:00 31/12/2017 00:00:00
135709 5 8 1 01/02/2017 00:00:00 30/04/2017 00:00:00
135709 5 8 1 01/04/2017 00:00:00 31/07/2017 00:00:00
 hidden batch(es)


IF OBJECT_ID('tempdb..#SRC1', 'U') IS NOT NULL DROP TABLE #SRC1; SELECT A.M_ID, A.Y_ID, A.T_ID, A.E_ID, A.BeginDate AS 'StartDate', A.EndDate, A.LeadStartDate, CASE WHEN A.EndDate > A.LeadStartDate THEN 'OverLap' WHEN A.EndDate = (A.LeadStartDate - 1) THEN 'Continuous' WHEN A.EndDate < A.LeadStartDate THEN 'Gap' WHEN A.LeadStartDate IS NULL THEN '' END AS 'RangeStatus', A.LeadEndDate INTO #SRC1 FROM ( SELECT PAR.M_ID, PAR.Y_ID, PAR.T_ID, PAR.E_ID, PAR.BeginDate, PAR.EndDate, LEAD(PAR.BeginDate) OVER (PARTITION BY PAR.M_ID, PAR.Y_ID, PAR.T_ID, PAR.E_ID ORDER BY PAR.BeginDate ASC ) AS 'LeadStartDate', LEAD(PAR.EndDate) OVER (PARTITION BY PAR.M_ID, PAR.Y_ID, PAR.T_ID, PAR.E_ID ORDER BY PAR.BeginDate ASC ) AS 'LeadEndDate' FROM #EventTable AS PAR ) AS A ORDER BY A.M_ID, A.Y_ID, A.T_ID, A.E_ID, A.BeginDate; --== IF OBJECT_ID('tempdb..#SRC2', 'U') IS NOT NULL DROP TABLE #SRC2; SELECT SRC1B.M_ID, SRC1B.Y_ID, SRC1B.T_ID, SRC1B.E_ID, MIN(SRC1B.StartDate) AS 'StartDate', SRC1B.EndDate INTO #SRC2 FROM ( SELECT SRC1A.M_ID, SRC1A.Y_ID, SRC1A.T_ID, SRC1A.E_ID, SRC1A.StartDate, MAX(SRC1A.EndDate) AS 'EndDate' FROM ( SELECT SRC1.M_ID, SRC1.Y_ID, SRC1.T_ID, SRC1.E_ID, SRC1.LeadStartDate AS 'StartDate', SRC1.LeadEndDate AS 'EndDate', 'Gap' AS 'RangeState' FROM #SRC1 AS SRC1 WHERE SRC1.RangeStatus = 'Gap' UNION SELECT SRC1.M_ID, SRC1.Y_ID, SRC1.T_ID, SRC1.E_ID, SRC1.StartDate AS 'StartDate', SRC1.LeadEndDate AS 'EndDate', 'Continuous' AS 'RangeState' FROM #SRC1 AS SRC1 WHERE SRC1.RangeStatus = 'Continuous' UNION SELECT SRC1.M_ID, SRC1.Y_ID, SRC1.T_ID, SRC1.E_ID, SRC1.StartDate AS 'StartDate', SRC1.LeadEndDate AS 'EndDate', 'OverLap' AS 'RangeState' FROM #SRC1 AS SRC1 WHERE SRC1.RangeStatus = 'OverLap' ) AS SRC1A GROUP BY SRC1A.M_ID, SRC1A.Y_ID, SRC1A.T_ID, SRC1A.E_ID, SRC1A.StartDate ) AS SRC1B GROUP BY SRC1B.M_ID, SRC1B.Y_ID, SRC1B.T_ID, SRC1B.E_ID, SRC1B.EndDate; -- IF OBJECT_ID('tempdb..#SRC3', 'U') IS NOT NULL DROP TABLE #SRC3; SELECT SRC2.M_ID, SRC2.Y_ID, SRC2.T_ID, SRC2.E_ID, SRC2.StartDate, SRC2.EndDate, LEAD(SRC2.StartDate) OVER (PARTITION BY SRC2.M_ID, SRC2.Y_ID, SRC2.T_ID, SRC2.E_ID ORDER BY SRC2.StartDate ASC ) AS 'LeadStartDate', CASE WHEN LEAD(SRC2.StartDate) OVER (PARTITION BY SRC2.M_ID, SRC2.Y_ID, SRC2.T_ID, SRC2.E_ID ORDER BY SRC2.StartDate ASC ) <= SRC2.EndDate THEN 'Overlap' ELSE '' END AS 'RangeStatus', LEAD(SRC2.EndDate) OVER (PARTITION BY SRC2.M_ID, SRC2.Y_ID, SRC2.T_ID, SRC2.E_ID ORDER BY SRC2.StartDate ASC ) AS 'LeadEndDate' INTO #SRC3 FROM #SRC2 AS SRC2; -- IF OBJECT_ID('tempdb..#SRC4', 'U') IS NOT NULL DROP TABLE #SRC4; SELECT SRC3.M_ID, SRC3.Y_ID, SRC3.T_ID, SRC3.E_ID, MIN(SRC3.StartDate) AS 'StartDate', MAX(SRC3.LeadEndDate) AS 'EndDate' INTO #SRC4 FROM #SRC3 AS SRC3 WHERE SRC3.RangeStatus = 'Overlap' GROUP BY SRC3.M_ID, SRC3.Y_ID, SRC3.T_ID, SRC3.E_ID; -- DELETE SRC3 FROM #SRC3 AS SRC3 INNER JOIN #SRC4 AS SRC4 ON SRC4.M_ID = SRC3.M_ID AND SRC4.Y_ID = SRC3.Y_ID AND SRC4.T_ID = SRC3.T_ID AND SRC4.E_ID = SRC3.E_ID AND ( SRC4.EndDate = SRC3.EndDate OR SRC4.StartDate = SRC3.StartDate ); -- SELECT SRC3.M_ID, SRC3.Y_ID, SRC3.T_ID, SRC3.E_ID, SRC3.StartDate, SRC3.EndDate FROM #SRC3 AS SRC3 WHERE SRC3.RangeStatus <> 'Overlap' UNION SELECT SRC4.M_ID, SRC4.Y_ID, SRC4.T_ID, SRC4.E_ID, SRC4.StartDate, SRC4.EndDate FROM #SRC4 AS SRC4 UNION SELECT SRC1.M_ID, SRC1.Y_ID, SRC1.T_ID, SRC1.E_ID, SRC1.StartDate, SRC1.EndDate FROM #SRC1 AS SRC1 WHERE SRC1.M_ID NOT IN ( SELECT M_ID FROM #SRC2 );
M_ID Y_ID T_ID E_ID StartDate EndDate
123457 4 2 3 01/05/2017 00:00:00 31/10/2017 00:00:00
134560 5 8 3 01/04/2017 00:00:00 31/08/2017 00:00:00
134560 5 8 3 01/10/2017 00:00:00 30/11/2017 00:00:00
135678 3 6 2 01/01/2017 00:00:00 30/06/2017 00:00:00
135678 3 6 2 01/10/2017 00:00:00 31/12/2017 00:00:00
135709 2 7 1 01/01/2017 00:00:00 30/06/2017 00:00:00
135709 2 7 3 01/01/2017 00:00:00 31/05/2017 00:00:00
135709 2 7 3 01/07/2017 00:00:00 31/08/2017 00:00:00
135709 2 7 3 01/10/2017 00:00:00 31/10/2017 00:00:00
135709 2 7 3 01/12/2017 00:00:00 31/12/2017 00:00:00
135709 5 8 1 01/02/2017 00:00:00 31/07/2017 00:00:00
 hidden batch(es)