So rather than create an Apex class, run multiple queries then write some code to figure out the answer, I thought I'd take a stab at writing a complex query that would get me the answer I'm looking for. Naturally, it's harder than I anticipated…
The goal is to calculate our 'renewal rate'. Here's the formula:
# of Accounts with both a current* closed/won opp and a previous* closed/won opp (divided by) # of Accounts with a current closed/won opp, regardless of previous opps
*'current' and 'previous' in this scenario are determined by two fields on the opp, 'effective date' and 'expiration date'. If today's date falls in between those two values, the opp is 'current'. An opp is a 'previous' opp if it is expired, but the expiration date falls within the past 365 days.
How can I get a parent-to-child relationship query to only return parent records if the child subquery returns results? For example:
SELECT ID, (SELECT StageName, Effective_Date__c, Expiration_Date__c FROM Opportunities WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY) FROM Account
Will return ALL my accounts, regardless of whether any matching opps are returned.
Apparently I can't do an aggregate query (eg "SELECT COUNT(ID)") that also has a child subquery. If that's the case, what's the best way to get a count of the Accounts that have the appropriate opportunities?
I'm having difficulty getting the list of accounts that have BOTH a current opp and a previous opp. If I modify my opp subquery's WHERE clause (from question 1) to say
WHERE StageName='Closed Won' AND (Effective_Date__c < TODAY AND Expiration_Date__c > TODAY) OR (Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS
It wont just return accounts with BOTH current previous opps – it will return accounts with Current opps, OR current and previous opps, OR just previous opps.
… Yeesh… it might be better to just write the code after all.. or do a cross-object SF report and just count the totals in Excel…
You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.
Accounts This Year
Accounts Last year
You can get the count like this
Accounts This Year
Accounts Last Year
You can use the same method, just combine the Where clauses from question 1