可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
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
回答1:
Select A.Customer_ID
,A.Team
,A.Type
,Group_Days_Criteria
,Start_Date = B.MinD
,End_Date = max(B.MaxD)
From @TempTable A
Cross Apply (
Select MinD=min(Start_Date),MaxD=Max(End_Date)
From @TempTable
Where (Start_Date between A.Start_Date and A.End_Date or
End_Date between A.Start_Date and A.End_Date)
and Customer_ID = A.Customer_ID
and Team = A.Team
and Type = A.TYPE
and Group_Days_Criteria = A.Group_Days_Criteria
) B
Group By
A.Customer_ID
,A.Team
,A.Type
,A.Group_Days_Criteria
,B.MinD
Returns
Customer_ID Team Type Group_Days_Criteria Start_Date End_Date
1 A A 28 2014-05-15 2015-05-13
1 A B 7 2014-05-15 2014-05-15
1 A B 7 2014-06-13 2015-05-13
回答2:
This is a more complicated question than I assumed when I first started playing with it. I had to come up with a totally different way of doing things than my previous answer. That answer would look back a row or two to compare dates. That is not sufficient since we have such irregular ranges for our consultations. In the end, the previous answer had a query that was very fragile in the real world, as we saw.
We need to loop through each consultation for each grouping of Customer, Team and Type - and compare our consult dates that way. This solution compares each consultation to the first consultation by grouping. It updates the end date of the first consultation if the row at hand falls within the date range of that consultation. If the row at hand falls outside of that date range, a row is inserted. Continuing on in our loops, we will no longer consider the first consultation. Future comparisons will be made to the row we inserted until consultations no longer fall within that second date range.
This completes in under a second on my machine.
I added a new answer so you can see where my first train of thought was not going to work from the start. Please let me know if you have any questions.
Setup and Return Results:
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)
-- renamed work table columns for ease of typing
-- note: try to avoid using reserved words (type) for column names
declare @prepTable table -- temp table with row number for loops, padded end date for comparison
(
[CustomerId] INT
,[Team] VARCHAR(1)
,[ConsultType] VARCHAR(1)
,[StartDate] DATETIME
,[EndDate] DATETIME
,[GroupDaysCriteria] INT
,ConsultEndDate datetime
,rn int
);
declare @ConsolidateConsults table -- consult work table
(
[CustomerId] INT
,[Team] VARCHAR(1)
,[ConsultType] VARCHAR(1)
,[StartDate] DATETIME
,[EndDate] DATETIME
,[GroupDaysCriteria] INT
,ConsultEndDate datetime
,ConsultGroup int
);
declare @CustomerTeamType table -- temp lookup table for loops
(
[CustomerId] INT
,CustomerNumber int
,[Team] VARCHAR(1)
,TeamId int
,[ConsultType] VARCHAR(1)
,ConsultTypeId int
,ConsultCount int
);
insert into @CustomerTeamType
select Customer_Id,
null,
Team,
null as TeamId,
[Type],
null as ConsultTypeId,
count(*)
from @TempTable
group by Customer_Id, Team, [Type];
update c
set CustomerNumber = ctt.CustomerNumber,
TeamId = ctt.TeamId,
ConsultTypeId = ctt.ConsultTypeId
from @CustomerTeamType c
inner join
(
select ct.CustomerId,
ct.Team,
ct.ConsultType,
dense_rank() over (partition by 1 order by ct.CustomerId) as CustomerNumber,
dense_rank() over (partition by ct.CustomerId order by ct.Team) as TeamId,
dense_rank() over (partition by ct.CustomerId, ct.Team order by ct.ConsultType) as ConsultTypeId
from @CustomerTeamType ct
) as ctt
on c.CustomerId = ctt.CustomerId
and c.Team = ctt.Team
and c.ConsultType = ctt.ConsultType;
insert into @prepTable
select Customer_Id,
Team,
[Type],
[Start_Date],
[End_Date],
Group_Days_Criteria,
dateadd(day, Group_Days_Criteria, [End_Date]),
row_number() over (partition by Customer_Id, Team, [Type] order by [Start_Date] asc, [End_Date] desc)
from @tempTable;
insert into @ConsolidateConsults
select CustomerId,
Team,
ConsultType,
StartDate,
EndDate,
GroupDaysCriteria,
ConsultEndDate,
1 -- rn of one indicates a sure consult begin date
from @prepTable
where rn = 1;
--prepare loop counters and end points
declare @consultGroup int = 1;
declare @customer int = 1;
declare @team int = 1;
declare @consultType int = 1;
declare @maxCustomer int = (select max(CustomerNumber) from @CustomerTeamType);
declare @maxTeam int;
declare @maxConsultType int;
declare @totalConsults int;
-- loop through each consult in each consult type in each consult team for each customer
while @customer <= @maxCustomer
begin
-- Get total conult teams for customer x
set @maxTeam = (select max(TeamId)
from @CustomerTeamType
where CustomerNumber = @customer
);
while @team <= @maxTeam
begin
-- get total consult types for customer x, team y
set @maxConsultType = (select max(ConsultTypeId) from @CustomerTeamType where CustomerNumber = @customer and TeamId = @team)
while @consultType <= @maxConsultType
begin
-- get total consults for customer x, team y, consult type z
set @totalConsults = (select ConsultCount from @CustomerTeamType where CustomerNumber = @customer and TeamId = @team and @consultType = ConsultTypeId)
-- reset to first consult
declare @consult int = 1
while @consult <= @totalConsults
begin
declare @row table ( -- table to hold one consult at a time
CustomerId int,
Team varchar(1),
ConsultType varchar(1),
StartDate datetime,
EndDate datetime,
GroupDaysCriteria int,
ConsultEndDate datetime,
ConsultGroup int
)
insert into @row
select pt.CustomerId,
pt.Team,
pt.ConsultType,
pt.StartDate,
pt.EndDate,
pt.GroupDaysCriteria,
pt.ConsultEndDate,
@consultGroup
from @prepTable pt
inner join @CustomerTeamType ctt
on pt.CustomerId = ctt.CustomerId
and pt.Team = ctt.Team
and pt.ConsultType = ctt.ConsultType
and ctt.CustomerNumber = @customer
and ctt.ConsultTypeId = @consultType
and ctt.TeamId = @team
and pt.rn = @consult
-- if row at hand falls within previous consult, update end date to greater of end dates
if exists (
select * from @row r
inner join @ConsolidateConsults cc
on r.CustomerId = cc.CustomerId
and r.ConsultType = cc.ConsultType
and r.Team = cc.Team
and cc.ConsultGroup = @consultGroup
and r.StartDate >= cc.StartDate
and r.StartDate <= cc.ConsultEndDate
)
Begin
update cc
set cc.ConsultEndDate = case when cc.ConsultEndDate > r.ConsultEndDate then cc.ConsultEndDate else r.ConsultEndDate end,
cc.EndDate = case when cc.EndDate > r.EndDate then cc.EndDate else r.EndDate end
from @ConsolidateConsults cc
inner join @row r
on r.CustomerId = cc.CustomerId
and r.Team = cc.Team
and @consultGroup = cc.ConsultGroup
and r.ConsultType = cc.ConsultType
and cc.ConsultGroup = @consultGroup
--set @updates = @updates + 1
end
-- if row at hand falls after existing consultation, create a new row and grouping
else
begin
-- increment consult grouping so next loop considers proper consultation
set @consultGroup = @consultGroup + 1
insert into @ConsolidateConsults
select CustomerId,
Team,
ConsultType,
StartDate,
EndDate,
GroupDaysCriteria,
ConsultEndDate,
@consultGroup
from @row
end
set @consult = @consult + 1 -- move to next record
delete @row -- delete record we have just compared
end -- finished individual consultations for customer x, team y, consult type z
set @consultType = @consultType + 1 --move to next consult type
set @consultGroup = 1 -- reset conultation grouping
end -- finished comparing for consult type
set @consultType = 1 -- reset consult type
set @consultGroup = 1 -- reset conultation grouping
set @team = @team + 1 -- move to next team
end -- finished consult teams
set @team = 1 -- reset consult team
set @consultGroup = 1 -- reset conultation grouping
set @customer = @customer + 1 -- move to next customer
end -- finished customer
-- get output
select CustomerId as Customer_Id,
Team,
ConsultType as [Type],
StartDate as [Start_Date],
EndDate as [End_Date],
from @ConsolidateConsults
order by CustomerId, Team, ConsultType, StartDate
Query Returns:
╔═════════════╦══════╦══════╦═════════════════════════╦═════════════════════════╦═════════════════════╗
║ Customer_Id ║ Team ║ Type ║ Start_Date ║ End_Date ║ Group_Days_Criteria ║
╠═════════════╬══════╬══════╬═════════════════════════╬═════════════════════════╬═════════════════════╣
║ 1 ║ A ║ A ║ 2013-08-07 00:00:00.000 ║ 2013-12-31 00:00:00.000 ║ 28 ║
║ 2 ║ B ║ A ║ 2015-05-15 00:00:00.000 ║ 2016-05-12 00:00:00.000 ║ 28 ║
║ 3 ║ B ║ A ║ 2015-01-12 00:00:00.000 ║ 2015-05-28 00:00:00.000 ║ 28 ║
║ 3 ║ C ║ A ║ 2013-05-27 00:00:00.000 ║ 2014-07-23 00:00:00.000 ║ 28 ║
║ 3 ║ C ║ A ║ 2015-01-12 00:00:00.000 ║ 2015-12-17 00:00:00.000 ║ 28 ║
║ 4 ║ A ║ B ║ 2013-07-09 00:00:00.000 ║ 2014-04-21 00:00:00.000 ║ 7 ║
║ 4 ║ A ║ B ║ 2014-04-29 00:00:00.000 ║ 2014-08-01 00:00:00.000 ║ 7 ║
║ 5 ║ A ║ A ║ 2014-05-15 00:00:00.000 ║ 2015-05-13 00:00:00.000 ║ 28 ║
║ 5 ║ A ║ B ║ 2014-05-15 00:00:00.000 ║ 2014-05-15 00:00:00.000 ║ 7 ║
║ 5 ║ A ║ B ║ 2014-06-13 00:00:00.000 ║ 2015-05-13 00:00:00.000 ║ 7 ║
║ 6 ║ A ║ A ║ 2015-02-17 00:00:00.000 ║ 2015-08-03 00:00:00.000 ║ 28 ║
║ 7 ║ A ║ A ║ 2015-03-30 00:00:00.000 ║ 2015-11-17 00:00:00.000 ║ 28 ║
╚═════════════╩══════╩══════╩═════════════════════════╩═════════════════════════╩═════════════════════╝
回答3:
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
