Obtener cálculo usando tres tablas en MySQL

Estoy tratando de calcular el costo total de cada tarea. Hay una tabla de asignación de tareas que tiene la tarea asignada a un empleado específico con fecha y hora de inicio a fin. Por otro lado, hay otra tabla que contiene la tasa de salario por hora de un empleado. Para una tarea, el costo será el número de horas que trabajan multiplicado por su tasa de apuestas.

Entonces, mi enfoque fue que convertí el tiempo de inicio y finalización en segundos usando TO_SECONDS y luego resté el final desde el inicio para calcular el total de horas que trabajó un empleado. Luego traté de extraer la tasa de apuestas de un empleado y luego la multipliqué. Sin embargo, mi salida no es precisa y, a veces, ni siquiera se acerca.

Mi enfoque parece ser lógico. Sin embargo, mi consulta o entendimiento no es correcto. En primer lugar, estoy presentando la estructura de mi tabla.

Tabla de tareas: dentro de esta tabla, almaceno varios detalles de la tarea, como el título, la ubicación, la fecha de inicio, etc.

Asignación de tareas: esta tabla contiene la información sobre qué tarea se asigna a qué empleado con hora de inicio y finalización para que trabaje el empleado.

Tabla relacionada con las apuestas de los empleados: esta tabla indica la tasa de salarios por hora de cada empleado.

Ahora les voy a mostrar mi consulta que escribí.

Enfoque 1:

 SELECT
 # we need the title of the task
 t.title,
 
 # total hours completed on this task by employees
 # dividing the diff by 3600 to get sec into hour form
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) totalHrs,
 
 # calculate the total cost by each employee and sum it up
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) * wrd.wages_rate cost
 FROM task t
 JOIN task_assignment ta on t.id = ta.task_id
 JOIN work_rights_detail wrd on ta.employee_id = wrd.account_id
 GROUP BY ta.task_id

Enfoque 2

SELECT
 # we need the title of the task
 t.title,
 # total hours completed on this task by employees
 # dividing the diff by 3600 to get sec into hour form
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) totalHrs,
 # calculate the total cost by each employee and sum it up
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600))
 *
 (SELECT wrd.wages_rate FROM work_rights_detail wrd WHERE wrd.account_id = ta.employee_id) cost
FROM task t
JOIN task_assignment ta on t.id = ta.task_id
GROUP BY ta.task_id

Salida: la salida de la tarea 1 es incorrecta. Debería ser (10 x 1) + (11,5 x 1) = 21,5 sin embargo, ¡obtengo 20!

¿Puedes decirme qué es exactamente lo que estoy haciendo mal aquí? Más específicamente, ¿puede describir por qué obtengo este resultado aparentemente correcto pero incorrecto?

A continuación incluyo la estructura de la tabla y los datos.

create table task
(
 id int auto_increment
 primary key,
 title varchar(255) not null,
 note text not null,
 location varchar(255) null,
 email varchar(255) null,
 status int(1) default 0 not null,
 commence varchar(255) not null,
 deadline varchar(255) not null,
 client_name varchar(255) not null,
 phone varchar(15) null,
 address varchar(255) null,
 estimated_budget float not null,
 upfront_payment float not null,
 expense float default 0 not null,
 created varchar(255) not null
)
 charset = latin1;
INSERT INTO camshine.task (id, title, note, location, email, status, commence, deadline, client_name, phone, address, estimated_budget, upfront_payment, expense, created) VALUES (1, 'Task 1', 'This is note.', 'Haverhill', 'root@data21.com', 0, '2022-04-16T12:00:00+0100', '2022-04-18T12:00:00+0100', 'Rootdata21', '01747520068', 'this is address.', 1000, 150, 0, '2022-04-15T17:07:56+0100');
INSERT INTO camshine.task (id, title, note, location, email, status, commence, deadline, client_name, phone, address, estimated_budget, upfront_payment, expense, created) VALUES (2, 'Task 2', 'This is note.', 'Haverhill', 'root@data21.com', 0, '2022-04-16T12:00:00+0100', '2022-04-18T12:00:00+0100', 'Rootdata21', '01747520068', 'this is address.', 1000, 150, 0, '2022-04-15T17:07:56+0100');
create table task_assignment
(
 id int auto_increment
 primary key,
 task_id int not null,
 employee_id int not null,
 start varchar(255) not null,
 finish varchar(255) not null
)
 charset = latin1;
INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (1, 1, 2, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');
INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (2, 1, 3, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');
INSERT INTO camshine.task_assignment (id, task_id, employee_id, start, finish) VALUES (3, 2, 3, '2022-04-16T13:00:00+0100', '2022-04-16T14:00:00+0100');
create table work_rights_detail
(
 account_id int not null
 primary key,
 ni varchar(32) not null,
 wages_rate float not null,
 work_limit int(3) not null
);
INSERT INTO camshine.work_rights_detail (account_id, ni, wages_rate, work_limit) VALUES (2, 'NI', 10, 48);
INSERT INTO camshine.work_rights_detail (account_id, ni, wages_rate, work_limit) VALUES (3, 'NI', 11.5, 48);

Solución del problema

Tienes que hacer la multiplicación por el salario dentro de la suma. Está calculando la suma de horas y luego multiplicándola por solo una de las tasas del grupo. Entonces, si la tarea fue realizada por varias personas, elige a una de ellas y usa su salario como tarifa para toda la tarea.

SELECT
 # we need the title of the task
 t.title,
 # total hours completed on this task by employees
 # dividing the diff by 3600 to get sec into hour form
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600)) totalHrs,
 # calculate the total cost by each employee and sum it up
 SUM(((TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600) * wrd.wages_rate) cost
FROM task t
JOIN task_assignment ta on t.id = ta.task_id
JOIN work_rights_detail wrd on ta.employee_id = wrd.account_id
GROUP BY ta.task_id

Para evitar duplicar el cálculo de los períodos de tiempo, puede hacerlo en una subconsulta o CTE.

WITH task_hours AS (
 SELECT 
 ta.task_id, 
 ta.employee_id, 
 (TO_SECONDS(ta.finish) - TO_SECONDS(ta.start)) / 3600 AS hours_worked
 FROM task_assignment AS ta
)
SELECT 
 t.title, 
 SUM(th.hours_worked) AS totalHrs, 
 SUM(th.hours_worked * wrd.wages_rate AS cost
FROM task AS t
JOIN task_hours AS th ON t.id = th.task_id
JOIN work_rights_detail wrd on th.employee_id = wrd.account_id
GROUP BY ta.task_id