3

I have following controller code snippet:

public List<User_Stats__c> getSalesReps(){
    Integer regnInt = (regn!=null) ? Integer.valueOf(regn) : -1;
    Sales_Rep=[Select Id, Month__c,Year__c, Rental_Revenue_Goal__c From User_Stats__c where Sales_Rep__c =: OSR and Region__c =: regnInt and District__c =: dist and Sales_Rep__c != null and Rental_Revenue_Goal__c != null ORDER BY Month__c];
    Sales_Rep.sort();
    return Sales_Rep;
}

I want to filter this data only for current month and two months after that. i.e. if the current month is Jan then I want data for Jan, Feb and March. How can I do this?

3

Something like this should work even when current month is December and the year boundary changes. Untested ;)

Date today = System.today();
Date monthPlus1 = today.addMonths(1), monthPlus2 = today.addMonths(2);

List<User_Stats__c> stats = [Select Id, Month__c,Year__c
    From User_Stats__c 
    where (Month__c = :today.month() AND Year__c = :today:year())
        OR (Month__c = :monthPlus1.month() AND Year__c = :monthPlus1.year())
        OR (Month__c = :monthPlus2.month() AND Year__c = :monthPlus2.year())
    ORDER BY Year__c, Month__c];

You'd have to wrap this whole WHERE clause in 1 AND so you can put back the rest of your criteria...


It does look a bit stupid. I guess you could make a Date formula field that would have DATE(Year__c, Month__c, 1) in the definition and then you could use the SOQL date range literals... But the formulas aren't indexed so a full table scan would be needed depending on how selective the other criteria are...

Then again if you'd make this field (either a formula field or something set by simple workflow) your users could benefit a lot from standard date filters in reports ;)

| improve this answer | |
  • wouldn't month() return an integer value? in my code, Month__c and Year__c are string value. i.e. though Month__c has values like 1,2,3.. they are in string format.. – Robin Jan 21 '14 at 15:28
  • Also I desperately need help with one more question. It will be great if you can help me with that..: salesforce.stackexchange.com/questions/24658/… – Robin Jan 21 '14 at 15:30
  • Peculiar DB design... Why, because otherwise year would be "2,014"? Check if it compiles anyway, if not - slap String.valueOf() around them or .toString() at the end maybe. – eyescream Jan 21 '14 at 15:31
  • Sure! Thanks for the help. I am getting into a meeting. I will check this first thing after than and let you know if it doesn't work. Much appreciated. – Robin Jan 21 '14 at 15:39
1

You can use SOQL relative date values for that:

  • THIS_MONTS
  • NEXT_N_MONTHS

The SOQL query will look like this:

Select Id From Account Where DateField__c = THIS_MONTH OR DateField__c = NEXT_N_MONTHS:2
| improve this answer | |
  • But in my case, the Month__c variable holds string values like "1", "2", "3" etc. Would it still work? – Robin Jan 21 '14 at 15:09
  • @Robin No it would not work, it works only with date fields. Try to use 12 if/else statements :) – Sergej Utko Jan 21 '14 at 15:15
  • 3
    @Robin Save yourself trouble and make a date field; you can have it set by a trigger or workflow rule from your existing fields. This gives you automatic indexing, will let you simplify your queries, and allow reports to run faster. – sfdcfox Jan 21 '14 at 16:38

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.