Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BroncoBoyBroncoBoy 

SOQL: Trying to dynamically change the month filter for a query

Here's my SOQL:
SELECT Summary_Id__c, SUM(EA_App_Count__c) appCount FROM CustomObject__c WHERE Full_Date__c = THIS_MONTH GROUP BY Summary_Id__c';

Here's what I'm trying to do:
Trying to run this SOQL 6 separate times, based on the current month, once per month for the previous 6 months date range. So, in essence, after each run, the filter date range would dynamically change look something like:
  1. WHERE Full_Date__c = THIS_MONTH -1 //Previous month
  2. WHERE Full_Date__c = THIS_MONTH - 2 //2 months ago
  3. WHERE Full_Date__c = THIS_MONTH - 3 //3 months ago
  4. WHERE Full_Date__c = THIS_MONTH - 4 //4 months ago
  5. WHERE Full_Date__c = THIS_MONTH - 5 //5 months ago
  6. WHERE Full_Date__c = THIS_MONTH - 6 //6 months ago
So here's my dillema:  how to produce a date range that is the equivalent to THIS_MONTH - #?  Is there a Date Literal or function that I can use?

Thanks in advance for any help you can offer!
-Bronco
Best Answer chosen by BroncoBoy
BroncoBoyBroncoBoy
As is often the case, I discovered a solution for my question a day later and dollar short...not sure if it's the BEST/MOST EFFICIENT solution - but it works.   Hope it helps somebody.  Uses 2 functions:

Date calculateStartDateParameter(Integer monthNumberPassedIn)
{
    Date dateToReturn;
        Date calculatedStartMonth = Date.Today().addmonths(- monthNumberPassedIn).toStartofMonth();
        dateToReturn = calculatedStartMonth;
    return dateToReturn;
}
Date calculateEndDateParameter(Integer monthNumberPassedIn)
{
    Date dateToReturn;
        Date calculatedStartMonth = Date.Today().addmonths(- monthNumberPassedIn + 1);
        Date calculatedStartDate = calculatedStartMonth.toStartofMonth().addDays(-1);
        dateToReturn = calculatedStartDate;
    return dateToReturn;
}

String dateRange = ' (Full_Date__c > ' + calculateStartDateParameter(monthNumber) + ' AND Full_Date__c < ' + calculateEndDateParameter(monthNumber) + ')';

String query = 'SELECT Summary_Id__c, SUM(EA_App_Count__c) appCount FROM CustomObject__c  WHERE ' + dateRange + ' GROUP BY Summary_Id__c';

List<sObject> sobjList = Database.query(query);