Ahh I thought the first where statement was for the subquery and the 2nd where was for my original select, so I joined the 2 where's together and I am getting the same error message. ALso made a change to how I joined the tables together slightly. I also tried moving the On portion of the join after the subquery where i was getting my error at and that produced an Internal SQL Server error so I figured where I had it was correct. Below is my updated code.

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 DatePart(m, EndOfMonthDate) = month(EndofMonthDate) AND DatePart(yyyy, EndOfMonthDate) = year(EndofMonthDate)

-- Select * 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 '12/31/03' AND on_contract <= EndOfMonthDate and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate 

Thanks for all your help so far,

Matt