我试图计算每项任务的总成本。有一个任务分配表,其中包含分配给特定员工的任务,以及开始到完成日期&时间另一方面,还有另一个表格,其中包含员工的小时工资率。对于一项任务,成本将是他们工作的小时数乘以他们的赌注率。
因此,我的方法是将start&完成时间到秒使用到秒,然后从开始减去完成时间,计算员工工作的总小时数。然后我试着提取员工的赌注率,然后乘以它。然而,我的输出不准确,有时甚至不接近。
我的看法似乎合乎逻辑。然而,我的质疑或理解是不对的。首先,我要展示我的桌子结构。
任务表:
在这个表中,我存储了各种任务细节,如标题、位置、开始日期等。
任务分配:
此表包含有关将哪个任务分配给哪个员工的信息,以及该员工工作的开始和完成时间。
员工赌注相关表:
此表列出了每位员工的小时工资率。
现在我将向你们展示我写的问题。
方法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
方法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
输出:
任务1的输出不正确。它应该是(10x1)+(11.5x1)=21.5,然而,我得到了20!
你能告诉我我到底做错了什么吗。更具体地说,你能描述一下为什么我得到这个看似正确但不正确的结果吗?
下面是表格结构和数据。
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);
