I am building a reporting graph of users workload showing active tasks, paused and not started but in my results for 'paused' I am also retrieving an active job
SELECT 'active' AS `status`,COUNT(DISTINCT TA.`task_id`) AS `tasks`,U.uID AS `user_id`,
CONCAT(U.`firstname`, ' ', U.`lastname`) AS `user`
FROM `tasks` TA
INNER JOIN `users` U on U.`uID`=TA.`scheduled_to_user`
INNER JOIN `ticket` T on T.`tID`=TA.`ticketFK`
INNER JOIN `task_activity_log` TL ON TL.`task_id`=TA.`task_id`
INNER JOIN `project` P ON P.`pID`=T.`pFK`
WHERE TA.`started_on` IS NOT NULL
AND TA.`completed_on` IS NULL
AND TL.`start_ts` IS NOT NULL
AND TL.`end_ts` IS NULL
AND T.`closed_on` IS NULL
GROUP BY U.`uID`
UNION
SELECT 'paused' AS `status`, COUNT(DISTINCT TA.`task_id`) AS `tasks`,U.uID AS `user_id`,
CONCAT(U.`firstname`, ' ', U.`lastname`) AS `user`
FROM `tasks` TA
INNER JOIN `system_users` U on U.`uID`=TA.`scheduled_to_user`
INNER JOIN `task_activity_log` TL ON TL.`task_id`=TA.`task_id`
INNER JOIN `icket` T on T.`tID`=TA.`ticketFK`
INNER JOIN `project` P ON P.`pID`=T.`pFK`
WHERE TA.`started_on` IS NOT NULL
AND TA.`completed_on` IS NULL
AND T.`closed_on` IS NULL
AND TL.`start_ts` IS NOT NULL
AND TL.`end_ts` IS NOT NULL
GROUP BY U.`uID`
UNION
SELECT 'not started' AS `status`, COUNT(DISTINCT TA.`task_id`) AS `tasks`,U.uID AS `user_id`,
CONCAT(U.`firstname`, ' ', U.`lastname`) AS `user`
FROM `tasks` TA
INNER JOIN `users` U on U.`uID`=TA.`scheduled_to_user`
INNER JOIN `ticket` T on T.`tID`=TA.`ticketFK`
INNER JOIN `project` P ON P.`pID`=T.`pFK`
WHERE TA.`started_on` IS NULL
AND TA.`completed_on` IS NULL
AND T.`closed_on` IS NULL
GROUP BY U.`uID`
The task_activity_log table looks something like this:
| id | task_id | start_ts | end_ts |
1 1 2017-02-28 15:47:34 2017-02-28 15:47:34
2 1 2017-02-28 15:47:34 (NULL)
3 2 2017-02-28 15:47:34 2017-02-28 15:47:34
4 2 2017-02-28 15:47:34 2017-02-28 15:47:34
id 2 is an active task. ive tried getting the max id (latest activity) for each task by trying things like
LEFT JOIN (SELECT MAX(`id`)AS `max_id`,`task_id` AS `task_id` FROM`task_activity_log`) MA ON MA.`task_id`=TA.`task_id`
or
(SELECT MAX(`id`) FROM`task_activity_log` A1 WHERE A1.`task_id`=TA.`task_id`) AS `max_id`
But I can't get my logic right on this (think i've been looking at it too long) Any help would be appreciated
What you're trying to do is filtering the task_activity_log with an aggregation of itself (last logged task activity).
What you need is something like
select ts.*
from 'task_activity_log' as 'ts'
join (select 'task_id', max('id') as 'id' from 'task_activity_log' group by 'task_id') as 'max_ts'
on ts.'task_id' = max_ts.'task_id' and ts.'id' = max_ts.'id'
This way you only take into account the rows of task_activity_log referring to the latest activity.