Finding Opportunities from a List of Accounts

Question

I am given a list which has 1000 accounts. I am asked to find opportunities between stage0-5 that are present on these 1000 accounts. I should differentiate these 1000 accounts into 2 ways.

1 If Account has opportunity which is in stage 0-5, it should be seperated from the account that has no opportunity or opportunity that is in stage 6 or above. I need to differentiate the 1000 accounts into 2 lists so that we know which account has opportunity and which doesn't.

Your reply would be really of great help. Thanks

Answer 1

Set AccIDs=new Set()// fill this set with account ids in your list List=[Select id from Account where Id IN (select AccountId from opportunity where stage='0' || stage='1'....... && AccountId IN AccIDs)];//this will result into parent accounts of opp having stages 0-5.

Now remove the obtained account IDs from the list of Ids you have to get the the ones having no opportunity or opportunity that is in stage 6 or above.

This will help you,just you need to modify or construct your query.throw kudos if you like my answer !

Answer 2

This may work:

1. Loop over all the Opportunities where Stage is between 0-5 and capture their AccountId.

2. Use Aggregate SOQl to count the number of Opportunities each Account is having. Something like this:

   [SELECT AccountId AcctId, Count(id) OppCount FROM Opportunity WHERE AccountId in: acctIds GROUP BY AccountId]

3. Now, you can loop over the result to differentiate account where count = 0 and count != 0.

Please try and lemme know if it works.

Answer 3

I believe you could use roll up summary fields to the accounts, and than make a report on the accounts filtered on the roll up summary results ?

Rather than receiving 1000 accounts, I'd ask for the selection criteria of these accounts, so that you can include these in your reports too.