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
