I am trying to get the Last completed task and next open task for a set of accountids.

I can get the Last completed by using the following query

SELECT id, (select activitydate from tasks WHERE isclosed= true order by activitydate  desc limit 1) FROM Account WHERE Id IN : AccountIds  

Any idea how i can get the next open activity (ie, the activity which is open and the nearest to current date)

    (SELECT Id FROM ActivityHistories ORDER BY ActivityDate DESC LIMIT 1), 
    (SELECT Id FROM OpenActivities ORDER BY ActivityDate ASC LIMIT 1) 
FROM Account

You could also tweak the query based on other parameters, but this should be a good start.

  • 2
    ActivityDate is the Due Date of the task and not the completed date. There is no guarantee that the first completed task is the last one that was closed. It might be better to order the ActivityHistories by the LastModified date, assuming that tasks are never touched after being completed – BarCotter Nov 17 '13 at 18:05
  • 2
    @BarCotter Definitely a valid point. Personally, I would probably have a trigger automatically adjust ActivityDate when the "is closed" flag flips from false to true (e.g. they close the task). Or have a hidden field that one could query on that's set to the close date of the task. Like I said, there's more than one way to approach the task, and it really depends on business needs. – sfdcfox Feb 7 '14 at 14:56

You can use an inner query on the openactivities .You can order by ASC for this and also filter by activity date > today()

SELECT Id,(SELECT Id FROM OpenActivities WHERE ActivityDate >TODAY ORDER BY ActivityDate ASC ) FROM Account

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.