Find missing contact date details

Question

I have the following table with Contacts details:

Table: tblContacts

CREATE TABLE tblContacts
(
    Series INT,
    ContacNumber INT,
    CDate DATETIME
);

Sample data:

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');

I want to find those contacts who never called on those dates and 1 or 2 days before and after call.

Note: I may crease before and after calls to any level like 1,2,3,4 so on.

Expected Output: The following output for just 1 day before and after calls.

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

My try: Following query works only for 1 day before and after calls but not for other than 1.

Query:

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

Answer 1

You could get the next date in a Series.

Which can be used to find the gaps between the dates.

Then glue those gaps to the result.

For Sql Server 2008 the data is first loaded into a temporary table.

IF OBJECT_ID('tempdb..#tmpContacts', 'U') IS NOT NULL
    DROP TABLE #tmpContacts; 

CREATE TABLE #tmpContacts
(
 Series INT NOT NULL,
 Rn INT NOT NULL,
 ContacNumber INT NOT NULL,
 CDate DATE NOT NULL,
 PRIMARY KEY (Series, Rn)
); 

INSERT INTO #tmpContacts (Series, ContacNumber, CDate, Rn)
SELECT Series, ContacNumber
, CAST(CDate AS DATE)
, ROW_NUMBER() OVER (PARTITION BY Series ORDER BY CAST(CDate AS DATE)) Rn
FROM tblContacts
WHERE CDate >=  CAST('2019-01-01' AS DATE)
    AND CAST(CDate AS DATE) <=  EOMONTH(EOMONTH(CAST('2019-01-01' AS DATE)))
  GROUP BY Series, ContacNumber, CAST(CDate AS DATE);

WITH CTE_CONTACTS AS
(
  SELECT Series, ContacNumber
  , CDate
  , CDate AS nextCDate
  , CAST(0 AS BIT) AS IsGap
  FROM #tmpContacts

  UNION ALL

  SELECT t1.Series, null
  , DATEADD(day,1,t1.CDate)
  , DATEADD(day,-1,t2.CDate)
  , 1
  FROM #tmpContacts t1
  LEFT JOIN #tmpContacts t2
    ON t2.Series = t1.Series
   AND t2.Rn = t1.Rn + 1
   AND t1.CDate < DATEADD(day,-1,t2.CDate)
)
SELECT c.Series, ContacNumber
, CONCAT(CONVERT(varchar,c.CDate,23), 
        CASE 
        WHEN c.IsGap=1
         AND DATEDIFF(day,c.CDate,c.nextCDate) > 0 
        THEN ' - ' + CONVERT(varchar,c.nextCDate,23) 
        END) AS Cdates
FROM CTE_CONTACTS c
ORDER BY c.Series, c.CDate;

Test on rextester here

In Sql Server 2012+ the window function LEAD can be used instead.

WITH CTE_CONTACTS AS
(
  SELECT Series, ContacNumber
  , CAST(CDate AS DATE) AS CDate
  , LEAD(CAST(CDate AS DATE)) OVER (PARTITION BY Series ORDER BY CAST(CDate AS DATE)) AS nextCDate
  FROM tblContacts
  WHERE CDate >=  CAST('2019-01-01' AS DATE)
    AND CAST(CDate AS DATE) <=  EOMONTH(EOMONTH(CAST('2019-01-01' AS DATE)))
  GROUP BY Series, ContacNumber, CAST(CDate AS DATE)
)
, CTE_CONTACTS2 AS
(
  SELECT Series, ContacNumber
  , CDate
  , nextCDate
  , CAST(0 AS BIT) AS IsGap
  FROM CTE_CONTACTS

  UNION ALL

  SELECT Series, null
   , DATEADD(day,1,CDate)
   , DATEADD(day,-1,nextCDate)
   , 1
  FROM CTE_CONTACTS c
  WHERE CDate < DATEADD(day,-1,nextCDate)
)
SELECT c.Series, ContacNumber
, CONCAT(CONVERT(varchar,c.CDate,23), 
        CASE 
        WHEN c.IsGap=1 
         AND DATEDIFF(day,c.CDate,c.nextCDate) > 0 
        THEN ' - ' + CONVERT(varchar,c.nextCDate,23) 
        END) AS Cdates
FROM CTE_CONTACTS2 c
ORDER BY c.Series, c.CDate;

A test on rextester here

Answer 2

You can generate numbers and then add in rows. Something like this:

select contactnumber, cdate, 1 as isvalid
from tblcontacts
union all
select c.contactnumber, dateadd(day, v.n, c.contacctdate), 0
from tblcontacts c cross join
     (values (-1), (1)) v(n)
where not exists (select 1
                  from tblcontacts c2
                  where c2.contactnumber = c.contactnumber and
                        c2.date = dateadd(day, v.n, c.contacctdate)
                 );

Note: I added a new column to determine if the row is valid. It doesn't make sense to repeat NULL values in the first column, because you don't know what contact number the row applies to.