5

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)

Improve this question

2 Answers 2

Reset to default
4 
SELECT Id, 
    (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.

Improve this answer
2
  • 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
    Commented Nov 17, 2013 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 ♦
    Commented Feb 7, 2014 at 14:56
1

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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .