In mysql database i've created leave table:
+--------+---------+---------+-------------+---------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE |
+--------+---------+---------+---------+------------+--------------------+------
| 1 | 1 | ALAN | MAX |2019-03-19 07:00:00 |2019-03-20 15:00:00 |
| 2 | 1 | ALAN | MAX |2019-03-21 07:00:00 |2019-03-21 15:00:00 |
+--------+---------+---------+----------------------+---------------------------
"Workers Table"
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 1 | ALAN | MAX |
| 2 | MARK | DARK |
+----------+---------+---------+
"Orders" Table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
"Order_status" Table:
+----------+---------+---------+---------------------+-------------------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE | END_DATE | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
| 30 | 1 | 20 |2019-03-18 06:50:35 |2019-03-18 15:21:32| NO |
| 31 | 1 | 20 |2019-03-20 06:44:12 |2019-03-20 15:11:23| NO |
| 32 | 1 | 20 |2019-03-22 06:50:20 |2019-03-22 12:22:33| YES |
| 33 | 2 | 20 |2019-03-18 06:45:11 |2019-03-18 15:14:45| NO |
| 34 | 2 | 20 |2019-03-20 06:50:22 |2019-03-20 15:10:32| NO |
| 35 | 2 | 20 |2019-03-22 06:54:11 |2019-03-22 11:23:45| YES |
+----------+---------+---------+------------+---------+-------------------+-----------+
What i've done:
I can to sumarize "total time" of each other workers (in order_status table) on the order including with sumarizing "leave time" from Leave table and sumarizing "Order time" + "leave time" too (partly). I have selected workers (LNAME, FNAME) orders (DESC_ORDER and NUMBER_ORDER) and "TOTAL TIME" on order from each other workers correctly too. I wrote the mysql command in below:
SELECT workers.fname,
workers.lname,
order_statusAgg.number_order,
workers.id_worker,
order_statusAgg.desc_order,
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME',
IFNULL(SEC_TO_TIME(SUM(leaveAgg.vtime)),'00:00:00') AS 'LEAVE TIME'
time_format(SEC_TO_TIME(SUM(order_statusAgg.stime)) + IFNULL(SEC_TO_TIME(SUM(leaveAgg.vtime)),'00:00:00'), '%H:%i:%s') AS 'TOTAL TIME'
FROM workers
LEFT JOIN (
SELECT leave.id_worker, time_format(SUM((datediff(leave.end_date, leave.begin_date) + 1) * (time(leave.end_date) - time(leave.begin_date))), '%H:%i:%s') AS vtime
FROM leave
GROUP BY leave.id_worker
) leaveAgg
ON leaveAgg.id_worker = workers.id_worker
LEFT JOIN (
SELECT order_status.id_worker, orders.number_order, orders.desc_order, SUM((Time_to_sec(order_status.end_date) -
Time_to_sec(order_status.begin_date))) AS stime
FROM order_status
INNER JOIN orders
ON orders.id_order = order_status.id_order
GROUP BY order_status.id_worker
) order_statusAgg
ON workers.id_worker = order_statusAgg.id_worker
WHERE order_statusAgg.number_order LIKE 'TEST'
GROUP BY workers.id_worker
Then i get the result:
+---------+---------+---------------+------------+------------+--------------+-----------+
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | ORDER TIME | LEAVE_TIME | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
| ALAN | MARK | TEST | TEST | 22:30:21 | 24:00:00 | 22:30:45 |
+---------+---------+---------------+------------+------------+--------------+-----------+
| MARK | GREEN | TEST | TEST | 21:19:18 | 00:00:00 | 21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+
But on the other hand this query has added only 24 seconds not 24 hours Because maybe of this line of query:
time_format(SEC_TO_TIME(SUM(order_statusAgg.stime)) + IFNULL(SEC_TO_TIME(SUM(leaveAgg.vtime)),'00:00:00'), '%H:%i:%s') AS 'TOTAL TIME'
that should be:
+---------+---------+---------------+------------+------------+--------------+-----------+
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | ORDER TIME | LEAVE_TIME | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
| ALAN | MARK | TEST | TEST | 22:30:21 | 24:00:00 | 46:30:21 |
+---------+---------+---------------+------------+------------+--------------+-----------+
| MARK | GREEN | TEST | TEST | 21:19:18 | 00:00:00 | 21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+
Because of for example: ALAN MARK for TEST order: Total time = Order time + leave time Total time = 22:30:21 + 24:00:00 = 46:30:21
Can someone show what kind of query should i write it? Any ideas? Thank you for any help or advice!
As you said Total time = Order time + leave time Total time = 22:30:21 + 24:00:00 = 46:30:21
so one way to solve this problem is use addtime function
ADDTIME('22:30:21',IFNULL('24:00:00','00:00:00')) as `TOTAL TIME`