I reworked the join, though I think you meant the active_units table and not the active_units_static, I almost got it 100% sytaxally working with one error saying

Line 18: Incorrect syntax near 'EOMDates'

I am not seeing anything wrong near line 18 which is right after the cross join. I do believe there are a few ) missing but I add them and still get the same error so I just removed them and the derived table did work so I am not 100% following where the incorrect syntax lies.

Below is the reworked query:

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 the_month = Month(EndofMonthDate) AND the_year = 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' -- adjust WHERE to suit date needs

where on_contract <= EndOfMonthDate and IsNULL(off_contract, EndOfMonthDate) >= EndOfMonthDate

Matt

P.S. If someone knows how to actually format the code so its more readable, let me know how to do it.