按列中的条件对非连续日期进行分组(跟进) [英] Group Non-Contiguous Dates By Criteria In Column (Follow Up)
本文介绍了按列中的条件对非连续日期进行分组(跟进)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在这篇文章中回答了这个问题，但有人建议我将其作为一个新问题重新提问:
I had this question answered in this post but have been advised to re-ask this as a new question:
但是，我注意到，虽然该解决方案在大多数情况下都有效，但仍有一些错误会在它似乎无法正确组合事物的地方漏出.
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.
例如
数据:
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)
看起来像这样:
+-------------+------+------+------------+------------+---------------------+
| 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 |
+-------------+------+------+------------+------------+---------------------+
目前是这样的:
+-------------+------+------+------------+------------+---------------------+
| 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 |
+-------------+------+------+------------+------------+---------------------+
我需要它像这样出来:
+-------------+------+------+------------+------------+---------------------+
| 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?
丹尼尔
推荐答案
我想我已经用 set 破解了它(虽然很乱)...
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
这篇关于按列中的条件对非连续日期进行分组(跟进)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
