Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Opportunity Close - only pull the latest date RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 = 2013

    SELECT
    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.actualend

    Any help is appreciated!

    Ken Compter

    Thursday, April 10, 2014 1:59 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 work

    Microsoft 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 PM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 work

    Microsoft 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 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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