How to capture the no of orders done for an account for at given month?

Question

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

Answer 1

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);
    }
}