SELECT TOP (100) PERCENT z.*, c.Capacity
FROM (
		SELECT a.wcm_id,a.project_id, a.datamartdate, a.plannedenddate,a.actualenddate, b.projectstate
		,CASE WHEN a.actualenddate IS NULL
		THEN DATEADD(mm, DATEDIFF(mm, 0, a.plannedenddate), 0)
		Else DATEADD(mm, DATEDIFF(mm, 0, a.actualenddate), 0)
		END AS Month
		,CASE WHEN b.projectstate IN ('Active')
		THEN 1
		ELSE NULL
		END AS Due
		,CASE WHEN a.plannedenddate IS NOT NULL AND a.actualenddate IS NOT NULL
		THEN CASE WHEN datediff(hh, a.PlannedEndDate, a.ActualEndDate) <= 0
			THEN 1 ELSE NULL
			END
		END AS Ontime
		,CASE WHEN a.plannedenddate IS NOT NULL AND a.actualenddate IS NOT NULL
		THEN CASE WHEN datediff(hh, a.PlannedEndDate, a.ActualEndDate)> 0
		THEN 1
			ELSE NULL
			END
		END AS Late
		,a.OBS, a.PIN, b.ProjectTitle, a.Capability, b.ManagementLead, a.WorkCoordinator, 
		b.ReportToLeadership,a.PlannedEndDate, GETDATE() AS Entered, a.DataMartDate

		FROM dbo.EPM_WCMilestones AS a
		JOIN dbo.EPM_Project AS b ON a.Project_ID = b.Project_ID

		WHERE a.PlannedEndDate IS NOT NULL and b.projectstate not in ('Inactive','Cancelled')
		and b.datamartdate = (select top 1 datamartdate from epm_project order by datamartdate desc)
		and a.datamartdate = (select top 1 datamartdate from epm_wcmilestones order by datamartdate desc)
	) z
LEFT JOIN EPM_Milestone_performance_Aggregate c
	ON z.month = c.month

ORDER BY wcm_id

quote:

---

Originally posted by dlorenc

I see the method... pretty slick... but got one error..

Msg 8156, Level 16, State 1, Line 1
The column 'PlannedEndDate' was specified multiple times for 'z'.

any ideas?

---

quote:

---

Originally posted by dlorenc

spoke too soon..grrr...I swear, it worked by removing those fields...

but when I saved it to a view..and tried to open it....I got this error..

Msg 4429, Level 16, State 1, Line 1
View or function 'dbo.EPM_Milestone_Performance_Aggregate' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'EPM_Milestone_performance_Aggregate' because of binding errors.

---