Getting calculation using three tables in MySQL

Question

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. Plus I have another table which also have various cost/expenses that has to be added to the sum to get the final cost for a task.

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.

Additional Cost: In this table I have cost that they each belongs to a task in the task table using the joining key task_id.

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

Approach 3 from the first answer:

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(ac.cost) as addiCost,
    SUM(th.hours_worked) AS totalHrs,
    SUM(th.hours_worked * wrd.wages_rate) AS cost
FROM task t
        JOIN task_hours AS th ON t.id = th.task_id
        JOIN work_rights_detail wrd on th.employee_id = wrd.account_id
        JOIN additional_cost ac on t.id = ac.task_id
GROUP BY t.id

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 lastest: With my latest query I have the followiwng output where I should have 100 as additional cost for task 1. And number of hours and task cost are inacturate again.

Output old: 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);


create table additional_cost
(
    task_id int          not null
        primary key,
    tag     varchar(255) not null,
    cost    float        not null,
    created varchar(255) not null
);

INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (1, 1, 'Additional cost', 49.5, '2022-04-15T17:07:56+0100');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (2, 2, 'tag', 10.5, '');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (3, 1, 'tag', 0.5, '');
INSERT INTO camshine.additional_cost (id, task_id, tag, cost, created) VALUES (4, 1, 'Additional cost', 50, '2022-04-15T17:07:56+0100');

It would be nice if you posted the input data as text so we can copy and paste to test this. — Barmar
– Barmar, Commented Apr 15, 2022 at 22:52

Barmar · Accepted Answer · 2022-04-15 23:22:01Z

2

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

edited Apr 15, 2022 at 23:22

answered Apr 15, 2022 at 23:08

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ahad Over a year ago

Thank you for your correct answer. Can you please tell me if there anything I should do to optimize this query?

Ahad Over a year ago

I can clearly see that calculating totalHrs is performed twice.

Barmar Over a year ago

Your version was calculating that twice, but mine doesn't, because the multiplication by wage_rate is inside one of them. The only thing that's being calculated twice is the difference between finish and start.

Barmar Over a year ago

If you wanted to avoid that duplication, you could move it into a CTE.

Ahad Over a year ago

I don't any idea about CTE. Could you please also include it in your answer. I really appreciate your advice and help. Thank you again.

|

Ahad · Accepted Answer · 2022-04-16 11:28:22Z

There is a couple of syntactical mistakes in the accepted answer which I have fixed in my ansewer to have a valid sql syntax and being able to run the query.

Moreover, for additional cost table I have also modified the query. I added the sql for calculating the additional cost as a sub query to say this: for each task go into the additional cost table and get all the cost under each task, sum it up and return it as addiCost.

So the final query which solves all the problem should be like the following. If anyone can tell me how it can even be more improved.

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,
    (SELECT SUM(adc.cost) c FROM additional_cost adc WHERE adc.task_id = t.id) as addiCost,
    SUM(th.hours_worked) AS totalHrs,
    SUM(th.hours_worked * wrd.wages_rate) AS cost
FROM task 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 t.id

Here is the outpu:

What I fixed was:

Added a matching closing parenthesis in the SUM function
fixed the alias ta(task_assignment) into th(task_hours) in the GROUP BY statement.

Collectives™ on Stack Overflow

Getting calculation using three tables in MySQL

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related