Consider the following table,
CREATE TABLE temp ( [TabName] VARCHAR(255), [ID] VARCHAR(255), [AsOfDate] DATE) INSERT INTO temp VALUES ('TAB1', 'C103', '2019-05-01'), ('TAB1', 'C103', '2019-06-01'), ('TAB2', 'C103', '2019-06-01'), ('TAB2', 'C103', '2019-07-01'), ('TAB1', 'C103', '2019-09-01'), ('TAB1', 'C104', '2019-06-01'), ('TAB1', 'C104', '2019-08-01')
TabName ID AsOfDate TAB1 C103 '2019-05-01' TAB1 C103 '2019-06-01' TAB2 C103 '2019-06-01' TAB2 C103 '2019-07-01' TAB1 C103 '2019-09-01' TAB1 C104 '2019-06-01' TAB1 C104 '2019-08-01' TAB1 C105 '2019-04-01' TAB1 C105 '2019-05-01'
I am wanting to find the IDs from the table that have skipped dates. In this table, I would want to identify that ID C103 and C104 have skipped dates as they have jumped from '2019-07-01' to '2019-09-01' and '2019-06-01' to '2019-8-01' respectively.
I have found the following question asked previously Find Missing Dates in Data which I believe gives some potential clues on the approach, i.e. using CTE, however, I am unsure how to apply them to this question where the dates are not consecutive across the whole table.
Should we be looking at partitioning here?