Show tasks which have taking the longest time sql

Question

I need to show the tasks which have taken the longest to complete. So far I have: (But I’m aware that it isn’t working as when I round the tables my results show both 1 and 2 months when it should be only showing 2, as 2 months is the longest running time

SELECT t.task_name,  max(round(months_between  t.start_date,  t.end_date)), e.employee_id, e.task_id
FROM task t, employee e
WHERE t.task_id = e.task_id;

please edit the question and include table definitions and sample data. thanks. — OldProgrammer, Dec 11 '18 at 13:34
Tip of today: Switch to modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. — jarlh, Dec 11 '18 at 13:39
It is really weird that `employee` has a `task_id`, instead of `task` having an `employee_id`. — Gordon Linoff, Dec 11 '18 at 14:03

score 0 · Answer 1 · answered Dec 11 '18 at 13:42

If you want to find the task or tasks (if there be a tie), then we can try using a rank analytic function:

WITH cte AS (
    SELECT t.task_id, t.task_name, SUM(t.end_date - t.start_date) AS days_spent,
        RANK() OVER (ORDER BY SUM(t.end_date - t.start_date) DESC) rnk
    FROM task t
    INNER JOIN employee e
        ON t.task_id = e.task_id
    GROUP BY t.task_id, t.task_name
)

SELECT task_id, task_name, days_spent
FROM cte
WHERE rnk = 1;

This answer assumes that the total time spent on a given task is comprised of the time spent by the individual employees associated with that task. Hence, we need to aggregate by task in the CTE to make it work.

score 0 · Accepted Answer · answered Dec 11 '18 at 13:44

Order by difference descending, use FETCH FIRST to get the row with max difference. Add WITH TIES, to get ties.

SELECT t.task_name, t.end_date - t.start_date AS diff, e.employee_id, e.task_id,
FROM task t
INNER JOIN employee e
    ON t.task_id = e.task_id
ORDER BY diff DESC
FETCH FIRST 1 ROW WITH TIES

Show tasks which have taking the longest time sql

2 Answers2