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.
- 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?