Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
In that case i have "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? Maybe something else?

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


What I have tried:

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.
Posted
Updated 25-Mar-19 9:50am

1 solution

You should be recording / generating "daily time records" to simplify things.

e.g.

day 1 7:00 - 15:00 xxx
day 2 7:00 - 12:00 xxx
day 2 12:00 - 15:00 sick

That way, it's a simple matter to "allocate time" over a particular period and work order items by selecting items that fall withing a particular date range.

You can "back calculate" what you need if you only have "sick records".
 
Share this answer
 
Comments
Member 10696161 25-Mar-19 17:14pm    
Can you show please how to do it or code?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900