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