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?