What is the best way to get Opportunity Stage Change History using SOQL?
Basically, what I need is the same list as in Opportunity page in Salesforce, where list of stages and dates of modifications are listed.
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this communityYou can use the OpportunityFieldHistory Object for this.
List<OpportunityFieldHistory> history = [Select Id, CreatedById, CreatedDate,
Field, NewValue, OldValue from OpportunityFieldHistory Where Field = 'StageName'];
You can use the OpportunityHistory object
SELECT StageName, Probability, Amount FROM OpportunityHistory where OpportunityId = {YourOppId}
if you are doing it in Apex, you can use relationship query like this
List<Opportunity> opps = [SELECT id,
(SELECT StageName,Probability,Amount FROM OpportunityHistories)
FROM Opportunity];
List<OpportunityHistory> histories = opp.OpportunityHistories
you can also use OpportunityFieldHistory which will give you the history of all the fields on the opportunity for which field tracking is enabled.