Getting calculation using three tables in MySQL-CodePudding

I am trying to calculate the total cost of each task. There is a task assignment table which has the task assigned to a specific employee with start to finish date & time. On the other hand, there is another table which holds the hour wages rate for an employee. For a task, the cost will be the number of hours they work multiply by their wagers rate.

So my approach was that I converted start & finish time to seconds using TO_SECONDS then substracted finish from the start to calculate total hours an employee worked. Then I tried to extract the wagers rate for an employee and then multiply it. However, my output is not accurate, and sometimes not even close.

My appraoch seems to be logical. However my query or understanding is not right. Firstly I am presenting my table structure.

Task Table: Within this table I store various task details such titile, location, commence date etc.

Task Assignment: This table holds the information regarding which task is assigned to which employee with start and finish time for the employee to work on.

Employee wagers related table: This table tells the hourly wages rate for each employee.

Now I am going to show you my query that I wrote.

Approach 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

Approach 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

Output: The output for task 1 is incorrect. It should be (10 x 1) (11.5 x 1) = 21.5 however, I'm getting 20!

Can you please tell me what exactly I'm doing wrong here. More specifically, can you please describe why I'm getting this seemingly correct but incorrect result?

Below I'm including the table structure and the data.

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', '[email protected]', 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', '[email protected]', 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);

CodePudding user response：

You need to do the multiplication by the wage rate within the sum. You're calculating the sum of hours, and then multiplying that by just one of the rates in the group. So if the task was done by multiple people, you're picking one of them and using their wage as the rate for the entire task.

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

To avoid duplicating the calculation of the time periods, you can do that in a subquery or 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