按列中的条件对不连续日期进行分组

Question

我有一个表格，上面有与客户进行团队咨询的开始和结束日期。

我需要根据另一列中指定的天数(有时咨询可能重叠，有时它们是连续的，有时不是(，团队和类型合并某些咨询。

一些示例数据如下所示：

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)

看起来像这样：

+-------------+------+------+------------+------------+---------------------+
| 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 |
+-------------+------+------+------------+------------+---------------------+

我想要的输出如下：

+-------------+------+------+------------+------------+---------------------+
| 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 | 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 |
+-------------+------+------+------------+------------+---------------------+

我根本就很难做到这一点，更不用说效率了！任何想法/代码都将受到极大的欢迎。

服务器版本是MS SQL Server 2014

谢谢

担

Answer 1

如果我

正确理解了您的问题，我们只想在上一次咨询结束日期后的group_days_criteria天内未进行第二次、第三次等咨询时才返回行。

我们可以获取上一个咨询结束日期，并消除在我们的日期范围内由同一团队和相同咨询类型为同一客户进行咨询的行(因为我们不关心咨询次数(。

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)
;with prep as (
select  Customer_ID,
        Team,
        [Type],
        [Start_Date],
        [End_Date],
        Group_Days_Criteria,
        ROW_NUMBER() over (partition by customer_id, team, [type] order by [start_date] asc, [end_date] desc) as rn, -- earliest start date with latest end date
        lag([End_Date] + Group_Days_Criteria, 1, 0) over (partition by customer_id, team, [type] order by [start_date] asc, [end_date] desc) as PreviousEndDate -- previous end date +
from @TempTable
)
select  p.Customer_Id,
        p.[Team],
        p.[Type],
        p.[Start_Date],
        p.[End_Date],
        p.Group_Days_Criteria
from prep p
where p.rn = 1 
    or (p.rn != 1 and p.[Start_date] > p.PreviousEndDate)
order by p.Customer_Id, p.[Team], p.[Start_Date], p.[Type]

这将返回所需的结果集。