I am trying to come with some logic to figure out what Opportunities have progressed in a week. One thing I was thinking of doing was getting the latest and earliest stages for the Opportunities from the Opportunity History table.

Then comparing the stages and this will indicate if an Opportunity has progressed or not.

So, do get the latest. I could do something like:

SELECT Amount, CloseDate, CreatedById, CreatedDate, IsDeleted, ExpectedRevenue, 
Id, OpportunityId, Probability, StageName, SystemModstamp, ForecastCategory 
FROM OpportunityHistory
WHERE OpportunityId = 'blah'
order by createdDate desc limit 1 

However, I need to do this for all Opportunities that belong to a certain bunch of Users. I am not sure how to do this. Any tips?

Thanks.

Attribution to: dublintech

Possible Suggestion/Solution #1

I ended up doing it by:

List<Opportunity> opps = [SELECT id, Probability, LastModifiedDate, OwnerId, Amount,
            (SELECT StageName,Probability,SystemModstamp FROM OpportunityHistories order by SystemModstamp desc) oppHistory
            FROM Opportunity 
            where LastModifiedDate = LAST_N_DAYS:7];

and then looping throu OpportunityHistory information for the Opportunities.

Attribution to: dublintech

Possible Suggestion/Solution #2

I would try it so:

First i would select a users, and then opportunity history.

List<Account> acc = [ Select Id From Account Where CreatedDate = LAST_WEEK ];

List<OpportunityHistory> oppHistory = [ Select OpportunityId, CloseDate, Amount 
                                        From OpportunityHistory
                                        Where Opportunity.AccountId IN :acc ];

Attribution to: Sergej Utko
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5276