I'm trying to get a list of Opportunities that have now "Cancelled" (isClosed=true and isWon=false) stageName and the previous (Penultimate) value was "Active" by SOQL .

For example, the Opp X had these history

  1. Created Active
  2. Updated Active
  3. Updated Paused
  4. Updated Active
  5. Updated Cancelled

This record should be return by the soql, but it shoudn't be returned if the step 4 doesn't exists.

In addition those opps should be and specific RecordType XX.

So far I've tried this:

Opportunity[] o =[select id from opportunity where isclosed = true and isWon = false  recordtype.name = 'XX'];
OpportunityHistory[] oh = [SELECT OpportunityId FROM OpportunityHistory WHERE StageName = 'Active' and OpportunityId in:o ];

But I cant figure out how to know that the second query is getting only the last previous Stage


  1. I have more than 10k Opps that should match
  • Does this have to be done in SOQL? What are you planning on doing with the records afterwards? Oct 15, 2014 at 12:15
  • That was the initial idea in order use it in a batch as a base query. But now it could be apex as well Oct 15, 2014 at 13:20

1 Answer 1


The OpportunityHistory does hold a record for each change of an opportunity. So if it went to stage Active, there will be a record for that. So it should be possible to execute the following query:

FROM Opportunity
WHERE IsClosed = true
AND IsWon = false
AND RecordType.name = 'XX'
    SELECT OpportunityId
    FROM OpportunityHistory
    WHERE StageName = 'Active'

It selects all Opportunities, which are not won and closed from a certain record type, if there is a history entry indicating that the Opoortunity has been Active once.

It is still debatable, if it will perform adequately with thousands of Opportunities and millions of OpportunityHistory entries. I would give it a try, since the query can be performed from the Developer Console without much effort.

  • Thank you for your reply, but this won work since I'm searching opps where only the penultimate status was 'Active'. Oct 15, 2014 at 14:27
  • @MartinBorthiry - penultimate - nice word!
    – Eric
    Oct 15, 2014 at 14:57
  • I must have overlooked the requrement with the funky name. I don't think that this can be solved in a single query. But you might try to query the OpportunityHistory for all closed/lost opportunities and order them by OpportunityId and CreatedDate. Then it is possible to iterate the list and inspect the last two history entries for each Opportunity. Oct 16, 2014 at 9:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .