Sorry Matt, I had the WHERE above the JOIN in my initial post... anyway, let's splitup the complexity...

 

-- Make EOMDates table 

If Object_ID('TempDB..#EOMDates') id Not NULL Drop Table #EOMDates 

Select * Into #Temp From 

	( 

	Select  Convert(DateTime, Convert(Varchar(8), DateAdd(m,  1-AMonth, DateAdd(yy,  -ManyYears, GetDate())), 1)) - DatePart(d, GetDate()) 

		As EndOfMonthDate 

	From ( 

		Select Number as AMonth 

		From Master.dbo.spt_Values  

		Where Type = 'P' and Number Between 1 and 12) Months 

	Cross Join ( 

		Select Number as ManyYears 

		From Master.dbo.spt_Values  

		Where Type = 'P' and Number Between 1 and 50) ManyYears -- may adjust the 5 to go back further 

	) EOMDates 

Where EndOfMonthDate Between '1/31/2000' and '11/30/03' -- adjust WHERE to suit date needs 

 

-- Now Simplified SELECT 

Select DatePart(m, EndOfMonthDate) as the_month, 

	DatePart(yyyy, EndOfMonthDate) as the_year, 

	isnull( (SELECT SUM(num_units) from active_units_static where month_end = EndOfMonthDate), 0)  

		+ count(termnum) as active_units  

from active_Units  

Join EOMDates 

	On on_contract <= EndOfMonthDate  

	and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate 

 

Let me know.