here is our requirement we need to get cases with case comments where case comment done 3 days ago for open cases(we are considering Processing, work in progress and pending closure cases as open cases). All this is to track pending(open) cases and we sent this to supporting team manager. In the following query we are getting all pending(open) cases but the problem is, the query showing cases worked on today (think child query is not running correctly, any suggestion?.
SOQL QUERY:
Select Id, Main_issue__c, Sub_issue__c, CaseNumber, status, Owner.Name,
(Select Id, CreatedBy.name, LastModifiedDate, CreatedDate
From CaseComments
WHERE CreatedDate >= LAST_N_DAYS:3
ORDER BY CreatedDate DESC LIMIT 1)
From Case
WHERE Status IN('Processing','Work in progress','Pending Closure')
ORDER BY Owner.Name
Best Answer
You would want to include a sub-query in the filter clause. The following query returns cases that have one of those three status values and do not have a case comment for at least three days.
Feel free to adjust this as you desire.