clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818564 fiddles created (9202 in the last week).

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')
7 rows affected
 hidden batch(es)


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
TabName Id AsOfDate Grp Rn
TAB1 C103 05/01/2019 00:00:00 1 1
TAB1 C103 06/01/2019 00:00:00 1 2
TAB1 C103 09/01/2019 00:00:00 1 3
TAB1 C104 06/01/2019 00:00:00 4 1
TAB1 C104 08/01/2019 00:00:00 4 2
TAB2 C103 06/01/2019 00:00:00 6 1
TAB2 C103 07/01/2019 00:00:00 6 2
 hidden batch(es)


;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
TabName Id AsOfDate
TAB1 C103 09/01/2019 00:00:00
TAB1 C104 08/01/2019 00:00:00
 hidden batch(es)


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
TabName Id AsOfDate
TAB1 C103 09/01/2019 00:00:00
TAB1 C104 08/01/2019 00:00:00
 hidden batch(es)