title: How to sumarize "sickness time" to the end of order - mysql
body: <p>In that case i have "sickness" table:</p>
  
  <pre><code>+--------+---------+---------+-------------+---------+-------------------------------+---------------+
  | 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    |
  +--------+---------+---------+----------------------+--------------------------------+---------------+  
  </code></pre>
  
  <p>"Workers" table:</p>
  
  <pre><code>+----------+---------+---------+
  |ID_WORKER |  FNAME  | LNAME   |
  +----------+---------+----------
  | 17       |  PAUL   |  KING   |
  | 18       |  SAM    |  BULK   |
  +----------+---------+---------+
  </code></pre>
  
  <p>"Orders" table:</p>
  
  <pre><code>+----------+--------------+---------------+
  |ID_ORDER  |  DESC_ORDER  | NUMBER_ORDER  |
  +----------+--------------+---------------+
  | 20       |  TEST        |  TEST         |
  +----------+--------------+---------------+
  </code></pre>
  
  <p>"Order_status" table:</p>
  
  <pre><code>+----------+---------+---------+---------------------+-------------------+------------+
  | 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       |
  +----------+---------+---------+------------+---------+-------------------+-----------+
  </code></pre>
  
  <p>What i've done:</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Then i get:</p>
  
  <pre><code>+---------+---------+---------------+------------+------------+--------------+
  |  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   |   
  +---------+---------+---------------+------------+------------+--------------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>+---------+---------+---------------+------------+------------+--------------+
  |  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   |   
  +---------+---------+---------------+------------+------------+--------------+
  </code></pre>
  
  <p>I'm wondering if that is about that code issue?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>