Answered by:
Opportunity Close - only pull the latest date

Question
-
I am writing an SSRS report using Opportunity Close entity, displaying Actual End date. When an Opportunity has been closed, reopened and closed a second time, the report shows both rows. Is there a way to only show the latest date?
Here is the query:
Declare @Year int;
Set @Year = 2013SELECT
FilteredOpportunityclose.owneridname,
FilteredSynact_Loan.synact_loantypename,
FilteredSynact_Loan.synact_commitment,
FilteredOpportunity.customeridname,
FilteredSynact_Loan.statuscodename,
FilteredOpportunityclose.description,
FilteredOpportunityclose.actualend
FROM FilteredOpportunity
INNER JOIN FilteredOpportunityClose ON FilteredOpportunity.opportunityid = FilteredOpportunityClose.opportunityid
INNER JOIN FilteredSystemUser ON FilteredSystemUser.fullname = FilteredOpportunity.owneridname
INNER JOIN FilteredSynact_Loan ON FilteredSynact_Loan.synact_opportunityid = FilteredOpportunity.opportunityid
WHERE(FilteredOpportunity.statuscode = '4' OR
FilteredOpportunity.statuscode = '5' OR
FilteredOpportunity.statuscode = '200000' OR
FilteredOpportunity.statuscode = '200001' OR
FilteredOpportunity.statuscode = '200002') AND
FilteredOpportunityclose.actualend >= '1/1/' + CAST(@Year AS NVARCHAR) AND
FilteredOpportunityclose.actualend <= '12/31/' + CAST(@Year AS NVARCHAR)ORDER BY
FilteredOpportunityclose.owneridname,
FilteredOpportunityclose.actualendAny help is appreciated!
Ken Compter
Thursday, April 10, 2014 1:59 PM
Answers
-
You could add the following to the WHERE clause:
AND FilteredOpportunityclose.actualend = (select max(oc2.actualend) from FilteredOpportunity o2 join FilteredOpportunityclose oc2 on o2.opportunityid = oc2.opportunityid and (o2.statuscode = '4' OR o2.statuscode = '5' OR o2.statuscode = '200000' OR o2.statuscode = '200001' OR o2.statuscode = '200002' ) AND oc2.actualend >= '1/1/' + CAST(@Year AS NVARCHAR) AND oc2.actualend <= '12/31/' + CAST(@Year AS NVARCHAR) AND oc2.opportunityid = FilteredOpportunity.opportunityid )
You may need to use table aliases in the main part of the query to make this workMicrosoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by kcompter Wednesday, April 16, 2014 5:33 PM
Thursday, April 10, 2014 3:42 PMModerator
All replies
-
You could add the following to the WHERE clause:
AND FilteredOpportunityclose.actualend = (select max(oc2.actualend) from FilteredOpportunity o2 join FilteredOpportunityclose oc2 on o2.opportunityid = oc2.opportunityid and (o2.statuscode = '4' OR o2.statuscode = '5' OR o2.statuscode = '200000' OR o2.statuscode = '200001' OR o2.statuscode = '200002' ) AND oc2.actualend >= '1/1/' + CAST(@Year AS NVARCHAR) AND oc2.actualend <= '12/31/' + CAST(@Year AS NVARCHAR) AND oc2.opportunityid = FilteredOpportunity.opportunityid )
You may need to use table aliases in the main part of the query to make this workMicrosoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by kcompter Wednesday, April 16, 2014 5:33 PM
Thursday, April 10, 2014 3:42 PMModerator -
David, thank you for your reply. I will try this out as soon as I can get back to the office and let you know how it works.
Ken Compter
Thursday, April 10, 2014 5:53 PM -
Here is what worked for me:
OUTER
APPLY
(
SELECT TOP 1 oc.*
FROM FilteredOpportunityClose oc
WHERE oc.opportunityid =
FilteredOpportunity.opportunityid
ORDER BY oc.actualend DESC
) FilteredOpportunityclose
Ken Compter
Wednesday, April 16, 2014 5:34 PM