SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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


;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


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

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