Missing Dates -TSQL Sample

  




create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-10-01' Union all
select 10, '2008-03-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'




;with mycte0 as (
 SELECT ID_No ,min(OccurMonth) dt  
 FROM #InputTable GROUP BY ID_No 
 )

,mycte1 as
(
SELECT ID_No,dt  FROM mycte0  
UNION ALL
SELECT ID_No, Dateadd (month, 1, dt ) FROM mycte1 b
WHERE EXISTS
(SELECT 1 FROM #InputTable it
WHERE it.ID_No = b.ID_No
AND it.OccurMonth > b.dt)
)
 
 
 
 
SELECT m.ID_No, m.dt
FROM   mycte1 m left join  #InputTable it
               ON it.ID_No = m.ID_No
                  AND it.OccurMonth = m.dt
WHERE  it.ID_No IS NULL
 


drop table #InputTable
 

---