Need to group up rows based on date overlapping

• squvi.87

  Default port

  Points: 1450

  More actions

  March 24, 2017 at 1:28 am

  #323036

  Hi There,
  In a same id, if any of row's effective date and enddate overlaps then we need group it up in a unique id

  In below image dategroup is the desired output column
  Data is sorted in order by ID asc, EffectiveDate ASC, EndDate Desc
  
  CREATE TABLE #DataTable (id int , EffectiveDate datetime, Enddate Datetime )

  INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, CAST(N'2017-01-01 00:00:00.000' AS DateTime), CAST(N'2017-01-11 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, CAST(N'2017-01-02 00:00:00.000' AS DateTime), CAST(N'2017-01-05 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, CAST(N'2017-01-03 00:00:00.000' AS DateTime), CAST(N'2017-01-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, CAST(N'2017-01-06 00:00:00.000' AS DateTime), CAST(N'2017-01-09 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, CAST(N'2017-01-13 00:00:00.000' AS DateTime), CAST(N'2017-01-19 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (2, CAST(N'2017-02-01 00:00:00.000' AS DateTime), CAST(N'2017-02-11 00:00:00.000' AS DateTime))
GO
INSERT [dbo].#DataTable ([id], [EffectiveDate], [Enddate]) VALUES (2, CAST(N'2017-02-06 00:00:00.000' AS DateTime), CAST(N'2017-02-16 00:00:00.000' AS DateTime))
GO


  Thanks in advance

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  March 24, 2017 at 2:52 am

  #1934704

  This is using a solution by Itzik Ben-Gan

  WITH C1 AS (
SELECT id, EffectiveDate, Enddate,
   CASE WHEN EffectiveDate <= MAX(Enddate) OVER(ORDER BY EffectiveDate, Enddate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS isstart
FROM #DataTable
)
SELECT id, EffectiveDate, Enddate,
   SUM(isstart) OVER(ORDER BY EffectiveDate, Enddate ROWS UNBOUNDED PRECEDING) AS DateGroup
FROM C1
ORDER BY EffectiveDate, Enddate;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Thom A

  SSC Guru

  Points: 99126

  More actions

  March 24, 2017 at 2:59 am

  #1934707

  Always check your SQL works before posting 😉 You don't declare a schema before the name of a temporary table. I was also getting conversion errors on your datetimes:
  CREATE TABLE #DataTable (id int , EffectiveDate datetime, Enddate Datetime);

  INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, '20170101 00:00:00.000', '20170111 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, '20170102 00:00:00.000', '20170105 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, '20170103 00:00:00.000', '20170112 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, '20170106 00:00:00.000', '20170109 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (1, '20170113 00:00:00.000', '20170119 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (2, '20170201 00:00:00.000', '20170211 00:00:00.000');
GO
INSERT #DataTable ([id], [EffectiveDate], [Enddate]) VALUES (2, '20170206 00:00:00.000', '20170216 00:00:00.000');
GO

  SELECT *
FROM #DataTable;
GO

  DROP TABLE #DataTable;
GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• squvi.87

  Default port

  Points: 1450

  More actions

  March 24, 2017 at 3:02 am

  #1934709

  Mark Cowne - Friday, March 24, 2017 2:52 AM

  This is using a solution by Itzik Ben-Gan

  WITH C1 AS (
SELECT id, EffectiveDate, Enddate,
   CASE WHEN EffectiveDate <= MAX(Enddate) OVER(ORDER BY EffectiveDate, Enddate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS isstart
FROM #DataTable
)
SELECT id, EffectiveDate, Enddate,
   SUM(isstart) OVER(ORDER BY EffectiveDate, Enddate ROWS UNBOUNDED PRECEDING) AS DateGroup
FROM C1
ORDER BY EffectiveDate, Enddate;

  awesome  working fine, can I have link to the original solution  by Itzik Ben-Gan. I would like to learn more about advanced tsql scripting.
  please suggest some links to learn

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  March 24, 2017 at 3:12 am

  #1934710

  squvi.87 - Friday, March 24, 2017 3:02 AM

  Mark Cowne - Friday, March 24, 2017 2:52 AM

  This is using a solution by Itzik Ben-Gan

  WITH C1 AS (
SELECT id, EffectiveDate, Enddate,
   CASE WHEN EffectiveDate <= MAX(Enddate) OVER(ORDER BY EffectiveDate, Enddate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS isstart
FROM #DataTable
)
SELECT id, EffectiveDate, Enddate,
   SUM(isstart) OVER(ORDER BY EffectiveDate, Enddate ROWS UNBOUNDED PRECEDING) AS DateGroup
FROM C1
ORDER BY EffectiveDate, Enddate;

  awesome  working fine, can I have link to the original solution  by Itzik Ben-Gan. I would like to learn more about advanced tsql scripting.
  please suggest some links to learn

  There's lots of great stuff by Itzik Ben-Gan online, here a link to get you started

  http://sqlmag.com/sql-server/new-solution-packing-intervals-problem

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537