А если так?
X++:
Select
ch.modifiedon
,t.name
,ch.new_commentary
From Filterednew_changeopportunity ch
Inner Join
(SELECT max(ch.modifiedon) AS d, opp.name AS name
FROM Filterednew_changeopportunity AS ch
INNER JOIN FilteredOpportunity AS opp ON ch.regardingobjectid = opp.opportunityid
WHERE (ch.actualend BETWEEN dbo.fn_BeginOfMonth(GETDATE()) AND GETDATE())
group by opp.name) t on ch.modifiedon = t.d