3

I have a few thousand records of historical orders say from 2006 for multiple accounts. I need to somehow capture the no of orders done for an account for every month from 2006.

I can do a query to get the no of orders done every month for each account using aggregate function count(). But what i want is the total orders done for each account from 2006 till the current month.

For example

For AccountA

There were

  • 3 orders done in Jan 2006
  • 2 orders done in Feb 2006
  • 0 orders done in Mar 2006
  • 4 orders done in Apr 2006

I can get the above using the SQOL Query

SELECT count(id),acc__c,CALENDAR_MONTH(orddate__c),CALENDAR_YEAR(orddate__c) FROM Order__c where orddate__c >= 2008-12-01 AND acc__c IN : accountId  group by acc__c,CALENDAR_YEAR(orddate__c),CALENDAR_MONTH(orddate__c)

I need to somehow capture the following information

  • Account A Jan 2006 - 3
  • Account A Feb 2006 - 5
  • Account A Mar 2006 - 5
  • Account A Apr 2006 - 9

and so on I am trying to generate these values using a batch process to avoid the governor limits. But i am very unsure of how to get the cumulative orders for each month Any ideas on how we can achieve this?

Thanks

  • Are 3 orders done in Jan 2006 and Account A Jan 2006 - 3 not the same? – Sergej Utko Feb 28 '13 at 11:28
  • Thats correct... But when i get to feb i need to add up jan and feb orders – Prady Feb 28 '13 at 11:51
3

You mean cumulative count / running total? I think you'll just have to loop through your query results adding up the numbers as you go.

In the past I've created helper classes to store (Account) Id, month, year and count to date for that purpose. (Your call if you store there whole Account or just id, remember about duplicated data, viewstate etc... Id is enough to refer to some map of accounts later on)

I'm not aware of any SOQL trick to achieve similar result. There's GROUP BY ROLLUP or even CUBE (I found ROLLUP quite useful, never really used CUBE) but they'll just add sub total per year, month, account and final grand total across all accounts (if you don't need some of these subtotals you can filter them out with HAVING.

EDIT for future reference:

Prady created a chat room. I'm going to post my idea how the code might look like (untested! probably doesn't even compile!) here too.

// 1. prepare helper wrapper class. Can be as simple as just a pair of integers to store your current month and cumulative to date
// If you have an sObject you want to save it into - use this instead :)
Wrapper {
    // Id accId; // you don't really need to store Acc Id and Date but it might help you later
    // Date startOfMonth;
    Integer monthlySales, yearToDateSales;

    Wrapper(Integer mon, Integer ytd){
        monthlySales = mon;
        yearToDateSales = ytd;
    }
}

// 2. Prepare storage.
Map<Id, Map<Date, Wrapper>> myMap = new Map<Id, Map<Date, Wrapper>>();

// 3. make query for MIN & MAX Date per each account, use this info to prepopulate with zeroes

// 4. Actual query
for(AggregateResult ar : [SELECT ...]){
    Id accId = String.valueOf(ar.get('Account__c');
    Date d = Date.newInstance(Integer.valueOf(ar.get('calYear')), Integer.valueOf(ar.get('calMon')), 1);
    Integer c = Integer.valueOf(ar.get('totalCount'));

    Map<Date, Wrapper> temp = myMap.get(accId);
    // Set the current sales value
    Wrapper w = myMap.get(d);
    w.monthlySales = c;
    w.yearToDateSales += c;

    // Loop within 'temp' account and for each date that's in same year(!) and > current date
    // add value to the yearToDateSales field so cumulatives will be calculated ok.
    Date tempDate = d;
    while(tempDate.year() == d.year()){
        w = myMap.get(tempDate);
        w.yearToDateSales += c;
        tempDate = tempDate.addMonths(1);
    }
}
|improve this answer|||||
  • another option i had thought of was to have map of maps. Parent map holding accountId and a map<Date,Integer>. Do you think its a right path? – Prady Feb 28 '13 at 11:56
  • For my most badass scenarios yes, I've been using Map<Id, Map<Date, Integer>> or Map<Id, List<Integer>>. But map of dates will have "random" order of keys so later you need to sort them somehow / retrieve in VF in order that's determined by helper list of dates? Do you need to fill gaps too (in your example - March). If it's for charts - Google chart will scale properly for gaps, VF chart won't (salesforce.stackexchange.com/questions/7858/…). Wanna go to chat? – eyescream Feb 28 '13 at 12:31
  • I am not using this data for charts i want to store them in a custom object and use that data to plot some reports.. Sure we can chat..But not sure how – Prady Feb 28 '13 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.