[SalesForce] SOQL query to get cases with case comment for open(processing) cases

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.

Select Id, Main_issue__c, Sub_issue__c, CaseNumber, status, Owner.Name
From Case 
WHERE Status IN('Processing','Work in progress','Pending Closure') 
      AND Id NOT IN (SELECT ParentId FROM CaseComment WHERE CreatedDate = LAST_N_DAYS:3)
ORDER BY Owner.Name

Feel free to adjust this as you desire.

Related Topic