Find missing months in SQL-CodePudding

So this post remains unanswered and not useful Finding missing month from my table

This link Get Missing Month from table requires a lookup table... which is not my first choice.

I have a table with Financial Periods, and a reference number. Each reference numbers has a series of financial periods which may start anywhere, and end anywhere. The test is simply that between the start and end, there is no gap - i.e. there must be every financial period period the smallest and largest dates, when grouped by reference number.

A financial period is a month.

So... in this example below, Reference Number A is missing May 2016.

REF    MONTH
A      2016-04-01
A      2016-06-01
A      2016-07-01
B      2016-03-01
B      2016-04-01
B      2016-05-01
C      2022-05-01



-- Find the boundaries of each ref
select REF, MIN(Month), MAX(Month) as smallest from myTable group by REF

-- But how to find missing items?

SQL Server 2019.

CodePudding user response：

Clearly a Calendar Table would make this a small task (among many others)

Here is an alternative using the window function lead() over()

Example

Declare @YourTable Table ([REF] varchar(50),[MONTH] date)  Insert Into @YourTable Values 
 ('A','2016-04-01')
,('A','2016-06-01')
,('A','2016-07-01')
,('B','2016-03-01')
,('B','2016-04-01')
,('B','2016-05-01')
,('C','2022-05-01')

;with cte as ( 
Select *
      ,Missing = datediff(MONTH,[Month],lead([Month],1) over (partition by Ref order by [Month]))-1
 From @YourTable
 )
 Select * from cte where Missing>0

Results

REF MONTH       Missing
A   2016-04-01  1