You need to sign in to do that
Don't have an account?
Query large dataset in aggregate SOQL query
I'm not sure that this is even possible but:
The requirement is to look at the last 90 days of cases by account and determine the average number of cases by account. THis is meant to be a running total - trying to find the top n accounts that require training at any given time. This functionality fires on update of the case.
Then I need to check those cases that match a certain Resolution code. (Training Required)
so:
for (Case c: cases){ system.debug('beforeAccountUpdate -->: Case: OwnerId: ' + c.OwnerId + ' Account: ' + c.AccountId + ' LastModifiedById: ' + c.LastModifiedById + ' Updated: ' + c.Updated__c); if (c.AccountId != null && c.Status <> 'Cancelled' && c.Type == 'Support'){ accountIds.add(c.AccountId); } } system.debug('\n accountIds -->: ' + accountIds ); AggregateResult[] allCases = [select accountid, count(id) from Case where CreatedDate > :dtCreate group by accountid]; for(AggregateResult ar : allCases) { System.debug('\n ------------------->intTtlCases = ' + intTtlCases); intTtlCases = intTtlCases + (Integer) ar.get('expr0'); } System.debug('\n ------------------->allCases = ' + allCases); System.debug('\n ------------------->allCases.size() = ' + allCases.size()); decAverage = intTtlCases/allCases.size(); System.debug('\n ------------------->decAverage = ' + decAverage); AggregateResult[] AccountCases = [select accountid, COUNT(id) from Case where CreatedDate > :dtCreate and accountid in: accountIds group by accountid]; for(AggregateResult ar : AccountCases) { AccCases.put((ID) ar.get('Accountid'), (Integer)ar.get('expr0')); System.debug('\n ------------------->Account id = ' + ar.get('accountid')); System.debug('\n ------------------->count = ' + ar.get('expr0')); System.debug('\n ------------------->AccCases = ' + AccCases); } // find all cases for all of the accounts in the last 90 days if(accountIds != null && accountIds.size() > 0){ List<Account> lstAccount = [select id, AccountCases__c, Account_Training_last_90_days__c, (select id from Cases where CreatedDate > :dtCreate and Resolution_Code__c in :lstResCodes) from Account where id in: accountIds]; system.debug('\n lstAccount -->: ' + lstAccount ); if(lstAccount.size() > 0){ for(Account acc: lstAccount){ if(acc.Cases.size() > 0){ acc.AccountCases__c = acc.Cases.size(); acc.Account_Cases_last_90_days__c = AccCases.get(acc.id); } } system.debug('\n lstAccount -->: ' + lstAccount ); // update the accounts with the number update lstAccount; } }
What I see is that the AggregateResult gets the first n results - not the whole database of <90 day old cases. That gives me an average which I know to be wrong from drawing a report into excel..
My question: How can I pull all of the cases, count them by account, get the average number for our client base then judge whether this account is in the top n, or not?