How sumarize leave time to max date of order - mysql

Question

0.00/5 (No votes)

See more:

In this case i have created leave table:

+--------+---------+---------+-------------+---------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE         | END_DATE             | 
+--------+---------+---------+---------+------------+--------------------+------
| 1      |   1     | ALAN  | MAX   |2019-03-22 07:00:00 |2019-03-23 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". 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,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME', 
IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)),'00:00:00') AS 'LEAVE TIME',
ADDTIME(SEC_TO_TIME(SUM(order_statusAgg.stime)), IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)), '00:00:00')) AS 'TOTAL TIME'
FROM workers
LEFT JOIN (SELECT leave.ID_WORKER, SUM((datediff(leave.BEGIN_DATE, leave.END_DATE) + 1) * (time_to_sec(time(leave.END_DATE)) - time_to_sec(time(leave.BEGIN_DATE)))) AS ltime FROM leave
LEFT JOIN
(SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' 
FROM order_status GROUP BY ID_WORKER) ordstat ON 
leave.ID_WORKER = ordstat.ID_WORKER 
WHERE leave.END_DATE <= MAX_END_DATE AND leave.BEGIN_DATE >= MIN_BEGIN_DATE 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:

+---------+---------+---------------+------------+------------+--------------+-----------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | ORDER TIME | LEAVE_TIME   | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
|  ALAN   |  MARK   | TEST          | TEST       | 22:30:21   |   08:00:00   |  30:30:21 |
+---------+---------+---------------+------------+------------+--------------+-----------+
|  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   00:00:00   |  21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+

ok. But PLEASE TAKE A LOOK: I added leave to Allan Max:

2019-03-19 07:00:00 |2019-03-23 15:00:00
2019-03-21 07:00:00 |2019-03-21 15:00:00

And get's only 8:00:00 of leave. Because this order ended on 22-03-2019. I'd like to get (despite of

2019-03-19 07:00:00 |2019-03-23 15:00:00

in Allan Max leave) the result:

+---------+---------+---------------+------------+------------+--------------+-----------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | ORDER TIME | LEAVE_TIME   | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
|  ALAN   |  MARK   | TEST          | TEST       | 22:30:21   |   16:00:00   |  38:30:21 |
+---------+---------+---------------+------------+------------+--------------+-----------+
|  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   00:00:00   |  21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+

2019-03-22 07:00:00 |2019-03-23 15:00:00 (to the end of order 2019-03-22) = 8:00:00 (maybe) 2019-03-21 07:00:00 |2019-03-21 15:00:00 (to the end of order 2019-03-22) = 8:00:00

So in LEAVE_TIME should be 8:00:00 + 8:00:00 = 16:00:00

It's that possible to change it? Can someone help me how to do it? thank you in advance for any help or advice.

What I have tried:

I've tried with this query:

SELECT workers.FNAME, 
workers.LNAME, 
order_statusAgg.NUMBER_ORDER,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME', 
IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)),'00:00:00') AS 'LEAVE TIME',
ADDTIME(SEC_TO_TIME(SUM(order_statusAgg.stime)), IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)), '00:00:00')) AS 'TOTAL TIME'
FROM workers
LEFT JOIN (SELECT leave.ID_WORKER, SUM((datediff(leave.BEGIN_DATE, leave.END_DATE) + 1) * (time_to_sec(time(leave.END_DATE)) - time_to_sec(time(leave.BEGIN_DATE)))) AS ltime FROM leave
LEFT JOIN
(SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' 
FROM order_status GROUP BY ID_WORKER) ordstat ON 
leave.ID_WORKER = ordstat.ID_WORKER 
WHERE leave.END_DATE <= MAX_END_DATE AND leave.BEGIN_DATE >= MIN_BEGIN_DATE 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;

But unfortunately i have no clue how to change it.

Posted 26-Mar-19 23:20pm

Member 10696161

Updated 27-Mar-19 6:07am

Add a Solution

Comments

#realJSOP 27-Mar-19 7:25am

Yes, it is unfortunate.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2019-03-27T06:08:00

1) You've got "ALAN" working and "on leave" at the same.
2) You put "order done" info at the "worker level" (twice) when it should be at the "order level"
3) "Start and end dates" on Orders will make it easier to allocate. You're having problems now because your design is weak.

+--------+---------+---------+-------------+---------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE         | END_DATE             | 
+--------+---------+---------+---------+------------+--------------------+------
| 1      |   1     | ALAN  | MAX   |2019-03-22 07:00:00 |2019-03-23 15:00:00   | 
+--------+---------+---------+----------------------+---------------------------

+----------+---------+---------+---------------------+-------------------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE          | END_DATE          | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
 |
| 32       |   1     |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES       |
 |
+----------+---------+---------+------------+---------+-------------------+-----------+