How to sumarize “sickness time” to the end of order - mysql

#1

In mysql database i’ve created “sickness” table:

+--------+---------+---------+-------------+---------+-------------------------------+---------------+
| Id_SICK|ID_WORKER| FNAME   | LNAME   | BEGIN_DATE          | END_DATE              | SICKNESS_TIME |
+--------+---------+---------+---------+------------+--------------------+-----------+---------------+
| 6      |   17    | PAUL    | KING    |2019-03-19 07:00:00  |2019-03-20 15:00:00    |    16:00:00   | 
| 7      |   17    | PAUL    | KING    |2019-03-25 07:00:00  |2019-03-25 15:00:00    |    8:00:00    |
+--------+---------+---------+----------------------+--------------------------------+---------------+  

“Workers” table:

+----------+---------+---------+
|ID_WORKER |  FNAME  | LNAME   |
+----------+---------+----------
| 17       |  PAUL   |  KING   |
| 18       |  SAM    |  BULK   |
+----------+---------+---------+

“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 |
+----------+---------+---------+----------+------------+---------+--------------------+
| 47       |   17    |    20   |2019-03-18 06:50:35  |2019-03-18 15:21:32|  NO        |
| 48       |   17    |    20   |2019-03-20 06:44:12  |2019-03-20 15:11:23|  NO        |
| 50       |   17    |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES       |
| 51       |   18    |    20   |2019-03-18 06:45:11  |2019-03-18 15:14:45|  NO        |
| 52       |   18    |    20   |2019-03-20 06:50:22  |2019-03-20 15:10:32|  NO        |
| 53       |   18    |    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 “sickness time” from Sickness table. 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 'TOTAL TIME', 
   IFNULL(SEC_TO_TIME(SUM(sickAgg.vtime)),'00:00:00') AS 'LEAVE TIME'
FROM workers 
LEFT JOIN (
SELECT leave.id_worker, SUM((datediff(sickness.end_date, sickness.begin_date) + 1) * (time_to_sec(time(sickness.end_date)) - time_to_sec(time(sickness.begin_date)))) AS vtime 
FROM sickness
GROUP BY sickness.id_worker) sickAgg
           ON sickAgg.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:

+---------+---------+---------------+------------+------------+--------------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | Sickness_time| 
+---------+---------+---------------+------------+------------+--------------+
|  PAUL   |  KING   | TEST          | TEST       | 22:30:21   |   24:00:00   |   
|  SAM    |  BULK   | TEST          | TEST       | 21:19:18   |   00:00:00   |   
+---------+---------+---------------+------------+------------+--------------+

Okey but on the other hand that order was finished in 23-03-2019. PAUL KING had a sickness in 25-03-2019 too and his sickness time shouldn’t added during this order which he was doing. So in this case that should be:

+---------+---------+---------------+------------+------------+--------------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | Sickness_time| 
+---------+---------+---------------+------------+------------+--------------+
|  PAUL   |  KING   | TEST          | TEST       | 22:30:21   |   16:00:00   |   
|  SAM    |  BULK   | TEST          | TEST       | 21:19:18   |   00:00:00   |   
+---------+---------+---------------+------------+------------+--------------+

I’m wondering if that is about that code issue?

LEFT JOIN (
SELECT leave.id_worker, SUM((datediff(sickness.end_date, sickness.begin_date) + 1) * (time_to_sec(time(sickness.end_date)) - time_to_sec(time(sickness.begin_date)))) AS vtime 
FROM sickness
GROUP BY sickness.id_worker) sickAgg
           ON sickAgg.id_worker = workers.id_worker

Has someone ideas how to deal with summarizing it till the end of duration of the order? Is it that possible? I was searching any idea but I’m not mysql guru. Thank you for any help.

0 Likes

#2

@Prochotap The most helpful thing I can tell you is that you are posting this in the wrong forum. Although this is about Databases it is not about helping with pure SQL queries.

You will get far more responses in a much quicker time by posting this question on

0 Likes