I need to find the missing months in a table 
for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-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'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

The other solutions I've found either:
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.

Try:

DECLARE @InputTable TABLE (ID_No int ,OccurMonth datetime)
insert into @InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-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 base AS (
SELECT ID_No, OccurMonth, ROW_NUMBER() OVER (PARTITION BY ID_No ORDER BY OccurMonth) AS seq
  FROM @InputTable
), rCTE AS (
SELECT ID_no, OccurMonth, seq, OccurMonth AS startMonth
  FROM base
 WHERE seq = 1
UNION ALL
SELECT r.ID_No, a.OccurMonth, a.seq, r.startMonth AS startMonth
  FROM rCTE r
    INNER JOIN base a
	  ON r.ID_No = a.ID_No
	  AND r.seq + 1 = a.seq
)


SELECT ID_No, OccurMonth
  FROM rCTE
 WHERE DATEADD(MONTH,(1-seq),OccurMonth) <> startMonth
 ORDER BY ID_No, seq

Your sample data and results don't make any sense.  Please describe what exactly you are trying to do.  If the month is "missing", how would and ID exist?

In any case, you would use a calendar table:

http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

create table #InputTable (ID_No int ,OccurMonth datetime)
 insert into #InputTable (ID_No,OccurMonth) 
 select 10, '2007-11-01' Union all
 select 10, '2007-12-01' Union all
 select 10, '2008-01-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'

SELECT TOP 1000
	d.[Date] 
FROM [dbo].[DimDate_ForumArticle] d
	LEFT OUTER JOIN #InputTable i
		ON i.OccurMonth = d.[date]
WHERE d.[DayNumber_of_Month] = 1
	AND i.[ID_No] IS NULL


DROP TABLE #InputTable;

I answered very similar question here

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6ad708d-9c06-4e7e-bc67-f11001ca9c74/finding-gaps-and-filled-with-the-last-validate-data?forum=transactsql#36854b32-4310-47af-9911-4e8c0f723ff9

The key to the solution is to have a Calendar table or a table populated with all the dates in between (in this case first day of the month).

Hi Brad,

To meet the 3 points listed, please see below sample.

;WITH Cte AS
(
SELECT ID_No,OccurMonth,ROW_NUMBER() OVER( PARTITION BY ID_No ORDER BY OccurMonth) RN FROM #InputTable
)
SELECT c.ID_No,CAST(DATEADD(MONTH,1,c.OccurMonth) AS DATE) startMon, CAST(DATEADD(MONTH,-1,c2.OccurMonth) AS DATE) EndMon FROM Cte c JOIN Cte c2 ON c.ID_No=c2.ID_No AND c.rn=c2.rn-1
WHERE DATEDIFF(MONTH,c.OccurMonth,c2.OccurMonth)>1

/*
ID_No	startMon	EndMon
20	2009-03-01	2009-03-01
30	2010-06-01	2010-07-01
*/

 

If you have any question, feel free to let me know.

This should work..

Select distinct
id_no,first_value(Occurmonth) over(partition by id_no order by occurmonth) OccurMonth
 from #InputTable
 union
 Select 
id_no,first_value(Occurmonth) over(partition by id_no order by occurmonth desc) OccurMonth
 from #InputTable

Thanks.