CREATE TABLE temp ( [TabName] VARCHAR(255), [ID] VARCHAR(255), [AsOfDate] DATE) INSERT INTO temp VALUES ('TAB1', 'C103', '2019-01-05'), ('TAB1', 'C103', '2019-01-06'), ('TAB2', 'C103', '2019-01-06'), ('TAB2', 'C103', '2019-01-07'), ('TAB1', 'C103', '2019-01-09'), ('TAB1', 'C104', '2019-01-06'), ('TAB1', 'C104', '2019-01-08')

SELECT TabName, Id, AsOfDate, RANK() OVER (ORDER BY TabName, Id) AS Grp, ROW_NUMBER() OVER (PARTITION BY TabName, Id ORDER BY AsOfDate) AS Rn FROM Temp

;WITH CTE AS ( SELECT TabName, Id, AsOfDate, RANK() OVER (ORDER BY TabName, Id) AS Grp, ROW_NUMBER() OVER (PARTITION BY TabName, Id ORDER BY AsOfDate) AS Rn FROM Temp ), CTE2 AS ( SELECT c1.TabName, c1.Id, c1.AsOfDate, c1.Grp, c1.Rn, 0 AS DateDiff FROM CTE c1 WHERE c1.Rn = 1 UNION ALL SELECT c1.TabName, c1.Id, c1.AsOfDate, c1.Grp, c1.Rn, DATEDIFF(DAY, c2.AsOfDate, c1.AsOfDate) AS DateDiff FROM CTE c1 INNER JOIN CTE2 c2 ON c2.Rn = c1.Rn - 1 AND c2.Grp = c1.Grp WHERE c1.Rn > 1 ) SELECT TabName, Id, AsOfDate FROM CTE2 WHERE DateDiff > 1 ORDER BY tabname, id, asofdate

SELECT TabName, Id, AsOfDate FROM ( SELECT TabName, Id, AsOfDate, ISNULL(DATEDIFF(DAY, LAG(AsOfDate) OVER (PARTITION BY TabName, Id ORDER BY AsOfDate), AsOfDate), 0) AS PrevDateDiff FROM Temp ) t WHERE t.PrevDateDiff > 1 ORDER BY tabname, id, asofdate