We have an Object Obj1 which consists of a field called Amount. Everyday many Obj1 records get created. I am finding it difficult to query for below conditions, can any of you please help:

  1. How to check if, for 5 days in a row in the last 30 days, any record has a negative Amount.
  2. How to check if, for any 18 days in the last 30 consecutive days, any record has a negative Amount.
  • What do you mean "amount negative for 5 consecutive days"? So, even if you have one -ve amount record, are you gonna count that as 1 or the sum of the day is -ve? kindly elaborate. – Mahmood Nov 17 '16 at 13:03
  • Hi Mahmood, thanks for the response. Amount can be positive or negative and we need to check amount which is negative(for any record on that particular day but not sum) for 5 consecutive days. In short, if we have records for 5 consecutive days called rec1, rec2(day1), rec3, rec4, rec5(day2), rec6(day3),rec7(day4),rec8(day5) and if rec1, rec3, rec6, rec7, rec8 have amount as negative then the condition is said to be satisfied. Hope it is clear. – Jaya Nov 17 '16 at 13:11
  • I don't think you can achieve something like this using SOQL. What you can do is create a field(hidden) and every time amount changes , +1 the value of the hidden field maybe using trigger and you can build your logic something with this. – Samir Nov 17 '16 at 13:56

I would look at collecting a Set<Date> as the first step in your logic.

Set<Date> datesWithNegative = new Set<Date>();
for (MyObject__c record : [
    SELECT CreatedDate FROM MyObject
    WHERE Amount__c < 0 AND CreatedDate = LAST_N_DAYS:30
]) datesWithNegative.add(record.CreatedDate.dateGmt());

With that, you can already satisfy your second requirement.

Boolean hasNegative_18of30 = datesWithNegative.size() > 17;

For the rest, you'll need to use a List so you can sort.

List<Date> orderedDatesWithNegative = new List<Date>(datesWithNegative);

Integer consecutive = 1;
Date pointer = orderedDatesWithNegative[0];
Boolean hasConsecutive_5 = false;

for (Date next : orderedDatesWithNegative)
    if (pointer.daysBetween(next) == 1) consecutive++;
    else consecutive = 1;
    pointer = next;
    if (consecutive > 4)
        hasConsecutive_5 = true;
  • I would hate to differ on your first logic, what if there are more than one negative records in a day? Even, second part also assumes that -ve record is only gonna be present / day. – Mahmood Nov 17 '16 at 14:43
  • The OP seems to indicate they only care about whether or not there are negatives for any day, not how many. It's still quite poorly worded but that's my interpretation. – Adrian Larson Nov 17 '16 at 14:44
  • I believe OP hasn't cleared that mist; but this assumption is rather very risky to me. Had it been that simple, sure, makes things easier. :-) – Mahmood Nov 17 '16 at 14:47
  • 1
    @AdrianLarson, yes you got it right, if atleast one negative record is found in a day then it will be taken into consideration. Thanks for your time. From your answer, for the second I feel consecutive should be 1 initially instead of 0, am I correct? – Jaya Nov 17 '16 at 15:09
  • @Jaya Good point, yes the day you are looking at does count as the first consecutive. – Adrian Larson Nov 17 '16 at 15:10

You can't do all that in SOQL; but you can use SOQL with Apex to achieve your goal.


SELECT id, amount FROM Obj1 WHERE CreatedDate = LAST_N_DAYS:30


List<obj1> obj1Records = [SELECT amount, createdDate 
                          FROM Obj1 
                          WHERE CreatedDate = LAST_N_DAYS:30 
                          AND Amound < 0
                          ORDER BY CreatedDate];

integer counter5, counter18 = 0;
date prevDate;

for(obj1 obj : obj1Records  )
    DateTime dT = obj.CreatedDate;
    date currDate = date.newinstance(dT.year(), dT.month(), dT.day());

    if(prevDate == null) prevDate = currDate;       // only set it if prevDate is null

    if (prevDate.daysBetween(currDate) == 1 )
        counter5 +=1;
        counter18 +=1;
        prevDate = currDate;        // add the counter for single day
    else if (prevDate.daysBetween(currDate) == 0)
        // dont do anything for the same day, but if the counters are already zero, then +1 them
        counter5 = counter5 > 0 ? counter5: 1;      
        counter18 = counter18 > 0 ? counter18: 1;           
    else if (prevDate.daysBetween(currDate) > 1)
        counter5 = 0;  // reset the counter5 as it has to look for consecutive -ve amounts.
        counter18 += 1;
        prevDate = currDate;    

    if(counter5 >= 5) system.debug('hey, 5 amounts have been negative');
    if(counter18 >= 18) system.debug('hey, 18 amounts have been negative');

P.S: I have not tested the above code. Found it interesting so spend some time to develop this logic.

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