找到丢失的联系细节

我与联系人详细信息如下表:

表:tblContacts

CREATE TABLE tblContacts ( Series INT, ContacNumber INT, CDate DATETIME ); 样本数据:

INSERT INTO tblContacts VALUES(12,123456,'2019-01-01'); INSERT INTO tblContacts VALUES(3,3456,'2019-01-01'); INSERT INTO tblContacts VALUES(12,123560,'2019-01-02'); INSERT INTO tblContacts VALUES(12,123459,'2019-01-05'); INSERT INTO tblContacts VALUES(3,3446,'2019-01-02'); INSERT INTO tblContacts VALUES(3,3486,'2019-01-03'); INSERT INTO tblContacts VALUES(3,34861,'2019-01-05'); INSERT INTO tblContacts VALUES(3,34862,'2019-01-07'); INSERT INTO tblContacts VALUES(12,127456,'2019-01-21'); INSERT INTO tblContacts VALUES(12,129456,'2019-02-03'); INSERT INTO tblContacts VALUES(12,126456,'2019-02-06'); INSERT INTO tblContacts VALUES(94,941256,'2019-01-01'); INSERT INTO tblContacts VALUES(94,944356,'2019-01-03'); INSERT INTO tblContacts VALUES(94,941356,'2019-01-07'); INSERT INTO tblContacts VALUES(94,943356,'2019-01-09'); 我想找那些从未接触呼吁那些日期和1或2天之前和之后的电话。

请注意:我可能折痕之前和之后调用任何级别1、2、3、4。

预期的输出:以下输出1天之前和之后调用。

ContacNumber CDate

3486 2019-01-03 NULL 2019-01-04 34861 2019-01-05 NULL 2019-01-06 34862 2019-01-07 123560 2019-01-02 NULL 2019-01-03 - 2019-01-04 123459 2019-01-05 NULL 2019-01-06 - 2019-01-20 127456 2019-01-21 NULL 2019-01-22 - 2019-02-02 129456 2019-02-03 NULL 2019-02-04 - 2019-02-05 126456 2019-02-06 941256 2019-01-01 NULL 2019-01-02 944356 2019-01-03 NULL 2019-01-04 - 2019-01-06 941356 2019-01-07 NULL 2019-01-08 943356 2019-01-09 我试一试:查询只能1天之前和之后调用而不是1。

查询:

; WITH Stage_1_CTE AS ( SELECT Series, ContacNumber, CAST(CDate AS DATE) CDate, ROW_NUMBER() OVER (PARTITION BY Series ORDER BY CAST(CDate AS DATE)) rnk1, (ROW_NUMBER() OVER (PARTITION BY Series ORDER BY CAST(CDate AS DATE)))/2 rnk2, (ROW_NUMBER() OVER (PARTITION BY Series ORDER BY CAST(CDate AS DATE)) + 1)/2 rnk3 FROM tblContacts GROUP BY ContacNumber,CDate,Series ) , Stage_2_CTE AS ( SELECT *, CASE WHEN rnk1%2=1 THEN MAX(CASE WHEN rnk1%2=0 THEN CDate END) OVER (PARTITION BY Series,rnk2) ELSE MAX(CASE WHEN rnk1%2=1 THEN CDate END) OVER (PARTITION BY Series,rnk3) END AS CDate_Prev, CASE WHEN rnk1%2=1 THEN MAX(CASE WHEN rnk1%2=0 THEN CDate END) OVER (PARTITION BY Series,rnk3) ELSE MAX(CASE WHEN rnk1%2=1 THEN CDate END) OVER (PARTITION BY Series,rnk2) END AS CDate_Next FROM Stage_1_CTE ) ,Stage_Final_CTE AS ( SELECT c.Series, c.ContacNumber, c.CDate, EndDate = '' FROM Stage_2_CTE c WHERE c.CDate <> DATEADD(DAY, +1, CDate_Prev) OR c.CDate <> DATEADD(DAY, -1, CDate_Next) UNION ALL SELECT Series, ContacNumber = NULL, CDate = DATEADD(DAY, 1, c.CDate), EndDate = ' - '+CAST(DATEADD(DAY, -1, CDate_Next) AS VARCHAR(10)) FROM Stage_2_CTE c WHERE c.CDate <> DATEADD(DAY, -1, CDate_Next) ) SELECT ContacNumber, CASE WHEN CAST(CDate AS VARCHAR(10)) = REPLACE(EndDate,' - ','')
THEN CAST(CDate AS VARCHAR(10)) ELSE CAST(CDate AS VARCHAR(10)) + CAST(EndDate AS VARCHAR(13)) END CDate FROM Stage_Final_CTE GROUP BY ContacNumber,CDate,EndDate,Series ORDER BY Series,CDate;