calculate times

[Destramic]

i have no idea how to do this in all honesty, hence my post.

i have a table called task_statuses, which records the statuses and times of a given task,  and those tasks are allocated to an engineer for the day.

before the engineer is at the task he/she will hit a button called Notify, which will send an sms and email to notify an engineer is on the way....when at the tasks the engineer has 3 further statuses to choose from depending on the outcome of task No Access, Completed or Rearranged.

 

task statuses
-------------------------------------------------------------                                                  
                                                       
Uncommenced                        default (no task status)                                                      
Notified                           start                                                      
No Access|Completed|Rearranged     end

-------------------------------------------------------------

If the engineer has no access at 8am, then there would be 2 statues added.

1. Notifiied

2. No Access

 

now the engineer may try again at 11am.  so before arriving the engineer will click a button called retry which will add Notified to the tasks_statuses and then is able to choose the 2nd outcome of the task

3. Notifed (Back to square one)

4. 2nd Outcome (Completed?)

 

what i want to do is caluclate the complete time spent on the task.

 

Here is a typical example:

1st attempt: 60 mins

2nd attempt: 28 mins.

Total task time : 88 mins

 

How is this possible please?

Thank you

 

[Barand]

It's case of "If you want to go there, I wouldn't start from here".

If the second visit were a separate task (so the first two as task #1 and second two are task #2, then life would be easier.

It's then simple (pseudocode)

SELECT MAX(created) - MIN(created) GROUP BY task_id.

You can then aggregate by each engineer for the day

 

[Destramic]

ok brillant, i'll do it that way, thank you for your help again barand

[Barand]

For the sake of completeness, given

mysql> select * from task;
+---------+---------+
| task_id | user_id |
+---------+---------+
|       1 |       1 |
|       2 |       1 |
|       3 |       2 |
+---------+---------+


mysql> select * from task_status;
+-------+---------+-----------+---------------------+
| ts_id | task_id | status    | created             |
+-------+---------+-----------+---------------------+
|     1 |       1 | Notified  | 2018-09-22 15:30:00 |
|     2 |       1 | No access | 2018-09-22 16:30:00 |
|     3 |       2 | Notified  | 2018-09-23 09:30:00 |
|     4 |       2 | Completed | 2018-09-23 09:58:00 |
|     5 |       3 | Notified  | 2018-09-22 10:30:00 |
|     6 |       3 | Completed | 2018-09-22 11:45:00 |
+-------+---------+-----------+---------------------+

then

SELECT 
       user_id
     , SEC_TO_TIME(SUM(time_to_sec(timetaken))) as totaltime
FROM
    task
    LEFT JOIN
        (
        SELECT task_id
             , timediff(MAX(created), MIN(created)) as timetaken
        FROM task_status
        GROUP BY task_id
        ) tots USING (task_id)
GROUP BY user_id;

+---------+-----------+
| user_id | totaltime |
+---------+-----------+
|       1 | 01:28:00  |
|       2 | 01:15:00  |
+---------+-----------+

 

[Destramic]

Beautiful stuff barand, although I've dropped the task statuses and added status, start time stamp and end timestamp to my tasks tablet and did it that way.  

Ps. I've still used what you've done for me previosuly but adding this will alter task times depending on task start and end, making things more accurate.

Thank you again for your efforts