I had this question answered in this post but have been advised to re-ask this as a new question:
Group Non-Contiguous Dates By Criteria In Column
However, I have noticed that while the solution works most of the time, there are some errors which trickle through where it doesn't seem to group things together correctly.
For Example,
Data:
DECLARE @TempTable TABLE([CUSTOMER_ID] INT
,[TEAM] VARCHAR(1)
,[TYPE] VARCHAR(1)
,[START_DATE] DATETIME
,[END_DATE] DATETIME
,[GROUP_DAYS_CRITERIA] INT)
INSERT INTO @TempTable VALUES (1,'A','A','2013-08-07','2013-12-31',28)
,(2,'B','A','2015-05-15','2015-05-28',28)
,(2,'B','A','2015-05-15','2016-05-12',28)
,(2,'B','A','2015-05-28','2015-05-28',28)
,(3,'C','A','2013-05-27','2014-07-23',28)
,(3,'C','A','2015-01-12','2015-05-28',28)
,(3,'B','A','2015-01-12','2015-05-28',28)
,(3,'C','A','2015-05-28','2015-05-28',28)
,(3,'C','A','2015-05-28','2015-12-17',28)
,(4,'A','B','2013-07-09','2014-04-21',7)
,(4,'A','B','2014-04-29','2014-08-01',7)
,(5,'A','A','2014-05-15','2015-04-24',28)
,(5,'A','A','2014-05-15','2015-04-24',28)
,(5,'A','A','2014-05-15','2014-05-15',28)
,(5,'A','A','2015-04-24','2015-05-13',28)
,(5,'A','B','2014-05-15','2014-05-15',7)
,(5,'A','B','2014-06-13','2015-04-24',7)
,(5,'A','B','2014-06-13','2015-04-24',7)
,(5,'A','B','2015-04-24','2015-05-13',7)
,(6,'A','A','2015-02-17','2015-04-28',28)
,(6,'A','A','2015-02-17','2015-04-28',28)
,(6,'A','A','2015-04-10','2015-04-28',28)
,(6,'A','A','2015-04-10','2015-04-28',28)
,(6,'A','A','2015-04-28','2015-06-04',28)
,(6,'A','A','2015-04-28','2015-08-03',28)
,(6,'A','A','2015-05-22','2015-08-03',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-04-28','2015-11-17',28)
,(7,'A','A','2015-04-28','2015-11-17',28)
,(7,'A','A','2015-05-12','2015-11-17',28)
,(7,'A','A','2015-05-12','2015-11-17',28)
Which looks like this:
+-------------+------+------+------------+------------+---------------------+
| CUSTOMER_ID | TEAM | TYPE | START_DATE | END_DATE | GROUP_DAYS_CRITERIA |
+-------------+------+------+------------+------------+---------------------+
| 1 | A | A | 07/08/2013 | 31/12/2013 | 28 |
| 2 | B | A | 15/05/2015 | 28/05/2015 | 28 |
| 2 | B | A | 15/05/2015 | 12/05/2016 | 28 |
| 2 | B | A | 28/05/2015 | 28/05/2015 | 28 |
| 3 | C | A | 27/05/2013 | 23/07/2014 | 28 |
| 3 | C | A | 12/01/2015 | 28/05/2015 | 28 |
| 3 | B | A | 12/01/2015 | 28/05/2015 | 28 |
| 3 | C | A | 28/05/2015 | 28/05/2015 | 28 |
| 3 | C | A | 28/05/2015 | 17/12/2015 | 28 |
| 4 | A | B | 09/07/2013 | 21/04/2014 | 7 |
| 4 | A | B | 29/04/2014 | 01/08/2014 | 7 |
| 5 | A | A | 15/05/2014 | 24/04/2015 | 28 |
| 5 | A | A | 15/05/2014 | 24/04/2015 | 28 |
| 5 | A | A | 15/05/2014 | 15/05/2014 | 28 |
| 5 | A | A | 24/04/2015 | 13/05/2015 | 28 |
| 5 | A | B | 15/05/2014 | 15/05/2014 | 7 |
| 5 | A | B | 13/06/2014 | 24/04/2015 | 7 |
| 5 | A | B | 13/06/2014 | 24/04/2015 | 7 |
| 5 | A | B | 24/04/2015 | 13/05/2015 | 7 |
| 6 | A | A | 17/02/2015 | 28/04/2015 | 28 |
| 6 | A | A | 17/02/2015 | 28/04/2015 | 28 |
| 6 | A | A | 10/04/2015 | 28/04/2015 | 28 |
| 6 | A | A | 10/04/2015 | 28/04/2015 | 28 |
| 6 | A | A | 28/04/2015 | 04/06/2015 | 28 |
| 6 | A | A | 28/04/2015 | 03/08/2015 | 28 |
| 6 | A | A | 22/05/2015 | 03/08/2015 | 28 |
| 7 | A | A | 30/03/2015 | 28/04/2015 | 28 |
| 7 | A | A | 30/03/2015 | 28/04/2015 | 28 |
| 7 | A | A | 30/03/2015 | 28/04/2015 | 28 |
| 7 | A | A | 30/03/2015 | 28/04/2015 | 28 |
| 7 | A | A | 28/04/2015 | 17/11/2015 | 28 |
| 7 | A | A | 28/04/2015 | 17/11/2015 | 28 |
| 7 | A | A | 12/05/2015 | 17/11/2015 | 28 |
| 7 | A | A | 12/05/2015 | 17/11/2015 | 28 |
+-------------+------+------+------------+------------+---------------------+
It is currently coming out like this:
+-------------+------+------+------------+------------+---------------------+
| Customer_Id | Team | Type | Start_Date | End_Date | Group_Days_Criteria |
+-------------+------+------+------------+------------+---------------------+
| 1 | A | A | 07/08/2013 | 31/12/2013 | 28 |
| 2 | B | A | 15/05/2015 | 12/05/2016 | 28 |
| 3 | B | A | 12/01/2015 | 28/05/2015 | 28 |
| 3 | C | A | 27/05/2013 | 23/07/2014 | 28 |
| 3 | C | A | 12/01/2015 | 28/05/2015 | 28 |
| 4 | A | B | 09/07/2013 | 21/04/2014 | 7 |
| 4 | A | B | 29/04/2014 | 01/08/2014 | 7 |
| 5 | A | A | 15/05/2014 | 24/04/2015 | 28 |
| 5 | A | B | 15/05/2014 | 15/05/2014 | 7 |
| 5 | A | B | 13/06/2014 | 24/04/2015 | 7 |
| 5 | A | A | 24/04/2015 | 13/05/2015 | 28 |
| 6 | A | A | 17/02/2015 | 28/04/2015 | 28 |
| 7 | A | A | 30/03/2015 | 28/04/2015 | 28 |
+-------------+------+------+------------+------------+---------------------+
I need it to come out like this:
+-------------+------+------+------------+------------+---------------------+
| CUSTOMER_ID | TEAM | TYPE | START_DATE | END_DATE | GROUP_DAYS_CRITERIA |
+-------------+------+------+------------+------------+---------------------+
| 1 | A | A | 07/08/2013 | 31/12/2013 | 28 |
| 2 | B | A | 15/05/2015 | 28/05/2015 | 28 |
| 3 | C | A | 27/05/2013 | 23/07/2014 | 28 |
| 3 | C | A | 12/01/2015 | 17/12/2015 | 28 |
| 3 | B | A | 12/01/2015 | 28/05/2015 | 28 |
| 4 | A | B | 09/07/2013 | 21/04/2014 | 7 |
| 4 | A | B | 29/04/2014 | 01/08/2014 | 7 |
| 5 | A | A | 15/05/2014 | 13/05/2015 | 28 |
| 5 | A | B | 15/05/2014 | 15/05/2014 | 7 |
| 5 | A | B | 13/06/2014 | 13/05/2015 | 7 |
| 6 | A | A | 17/02/2015 | 03/08/2015 | 28 |
| 7 | A | A | 30/03/2015 | 17/11/2015 | 28 |
+-------------+------+------+------------+------------+---------------------+
Any ideas on how I could fix this whilst still maintaining the criteria for the correct output?
Daniel
I think I have cracked it using a set (messy though)...
DECLARE @TempTable TABLE([CUSTOMER_ID] INT
,[TEAM] VARCHAR(1)
,[TYPE] VARCHAR(1)
,[START_DATE] DATETIME
,[END_DATE] DATETIME
,[GROUP_DAYS_CRITERIA] INT)
INSERT INTO @TempTable VALUES (1,'A','A','2013-08-07','2013-12-31',28)
,(2,'B','A','2015-05-15','2015-05-28',28)
,(2,'B','A','2015-05-15','2016-05-12',28)
,(2,'B','A','2015-05-28','2015-05-28',28)
,(3,'C','A','2013-05-27','2014-07-23',28)
,(3,'C','A','2015-01-12','2015-05-28',28)
,(3,'B','A','2015-01-12','2015-05-28',28)
,(3,'C','A','2015-05-28','2015-05-28',28)
,(3,'C','A','2015-05-28','2015-12-17',28)
,(4,'A','B','2013-07-09','2014-04-21',7)
,(4,'A','B','2014-04-29','2014-08-01',7)
,(5,'A','A','2014-05-15','2015-04-24',28)
,(5,'A','A','2014-05-15','2015-04-24',28)
,(5,'A','A','2014-05-15','2014-05-15',28)
,(5,'A','A','2015-04-24','2015-05-13',28)
,(5,'A','B','2014-05-15','2014-05-15',7)
,(5,'A','B','2014-06-13','2015-04-24',7)
,(5,'A','B','2014-06-13','2015-04-24',7)
,(5,'A','B','2015-04-24','2015-05-13',7)
,(6,'A','A','2015-02-17','2015-04-28',28)
,(6,'A','A','2015-02-17','2015-04-28',28)
,(6,'A','A','2015-04-10','2015-04-28',28)
,(6,'A','A','2015-04-10','2015-04-28',28)
,(6,'A','A','2015-04-28','2015-06-04',28)
,(6,'A','A','2015-04-28','2015-08-03',28)
,(6,'A','A','2015-05-22','2015-08-03',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-03-30','2015-04-28',28)
,(7,'A','A','2015-04-28','2015-11-17',28)
,(7,'A','A','2015-04-28','2015-11-17',28)
,(7,'A','A','2015-05-12','2015-11-17',28)
,(7,'A','A','2015-05-12','2015-11-17',28)
DECLARE @Holding TABLE([CUSTOMER_ID] INT
,[TEAM] VARCHAR(1)
,[TYPE] VARCHAR(1)
,[START_DATE] DATETIME
,[END_DATE] DATETIME
,[GROUP_DAYS_CRITERIA] INT
,[START_ROW_ORDER] INT
,[END_ROW_ORDER] INT)
INSERT INTO @Holding
SELECT TT.[CUSTOMER_ID]
,TT.[TEAM]
,TT.[TYPE]
,TT.[START_DATE]
,TT.[END_DATE]
,TT.[GROUP_DAYS_CRITERIA]
,ROW_NUMBER() OVER (PARTITION BY TT.[CUSTOMER_ID],TT.[TEAM],TT.[TYPE] ORDER BY TT.[START_DATE]) [START_ROW_ORDER]
,ROW_NUMBER() OVER (PARTITION BY TT.[CUSTOMER_ID],TT.[TEAM],TT.[TYPE] ORDER BY CASE WHEN TT.[END_DATE] IS NULL THEN 1 ELSE 0 END,TT.[END_DATE]) [END_ROW_ORDER]
FROM @TempTable TT
SELECT DISTINCT C.[CUSTOMER_ID]
,C.[TEAM]
,C.[TYPE]
,CASE WHEN C.[GROUP_ON_PREVIOUS] = 1 THEN LAG(C.[START_DATE]) OVER (PARTITION BY C.[CUSTOMER_ID],C.[TEAM],C.[TYPE] ORDER BY C.[START_DATE])
ELSE C.[START_DATE]
END [START_DATE]
,CASE WHEN C.[GROUP_ON_NEXT] = 1 THEN LEAD(C.[END_DATE]) OVER (PARTITION BY C.[CUSTOMER_ID],C.[TEAM],C.[TYPE] ORDER BY C.[START_DATE])
ELSE C.[END_DATE]
END [END_DATE]
FROM(SELECT A.[CUSTOMER_ID]
,A.[TEAM]
,A.[TYPE]
,A.[START_DATE]
,B.[END_DATE]
,CASE WHEN A.[START_DATE] <= DATEADD(DAY,A.[GROUP_DAYS_CRITERIA],LAG(B.[END_DATE]) OVER (PARTITION BY A.[CUSTOMER_ID],A.[TEAM],A.[TYPE] ORDER BY A.[START_ROW_ORDER])) THEN 1
ELSE 0
END [GROUP_ON_PREVIOUS]
,CASE WHEN DATEADD(DAY,A.[GROUP_DAYS_CRITERIA],B.[END_DATE]) >= LEAD(A.[START_DATE]) OVER (PARTITION BY A.[CUSTOMER_ID],A.[TEAM],A.[TYPE] ORDER BY A.[START_ROW_ORDER]) THEN 1
ELSE 0
END [GROUP_ON_NEXT]
FROM @Holding A
INNER JOIN @Holding B ON A.[CUSTOMER_ID] = B.[CUSTOMER_ID]
AND A.[TEAM] = B.[TEAM]
AND A.[TYPE] = B.[TYPE]
AND A.[START_ROW_ORDER] = B.[END_ROW_ORDER]) C
WHERE NOT (C.[GROUP_ON_PREVIOUS] = 1 AND C.[GROUP_ON_NEXT] = 1)
Returns rows in less than a second