Как суммировать время отпуска до максимальной даты заказа - mysql

---

В этом случае я создал таблицу leave:

+--------+---------+---------+-------------+---------+--------------------------
|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   |
+--------+---------+---------+----------------------+---------------------------

Таблица "Рабочие"

+----------+---------+---------+
|ID_WORKER |  FNAME  | LNAME   |
+----------+---------+----------
| 1        |  ALAN   |  MAX    |
| 2        |  MARK   |  DARK   |
+----------+---------+---------+

"таблица заказов:

+----------+--------------+---------------+
|ID_ORDER  |  DESC_ORDER  | NUMBER_ORDER  |
+----------+--------------+---------------+
| 20       |  TEST        |  TEST         |
+----------+--------------+---------------+

Таблица "Order_status" :

+----------+---------+---------+---------------------+-------------------+------------+
| 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       |
+----------+---------+---------+------------+---------+-------------------+-----------+ 

Что я сделал:

Я могу суммировать "общее время" друг друга работников (в таблице order_status) по порядку, в том числе с суммированием "время отпуска" из таблицы отпуска и суммированием "время заказа" + "время отпуска". Я также правильно выбрал рабочие (LNAME, FNAME) заказы (DESC_ORDER и NUMBER_ORDER) и "общее время" по заказу друг от друга рабочих. Я написал команду mysql ниже:

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;

Тогда я получаю:

+---------+---------+---------------+------------+------------+--------------+-----------+
|  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 |
+---------+---------+---------------+------------+------------+--------------+-----------+

ладно. Но, пожалуйста, взгляните: Я добавил Отпуск для Аллана Макса:

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

А сейчас только 8:00:00 отпуска. Потому что этот заказ закончился 22-03-2019 года. Я бы хотел получить (несмотря на то, что

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

в Алане максимум уходить) результат:

+---------+---------+---------------+------------+------------+--------------+-----------+
|  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 (до конца заказа 2019-03-22) = 8:00:00 (возможно) 2019-03-21 07:00:00 |2019-03-21 15:00:00 (до конца заказа 2019-03-22) = 8:00:00

Так что в LEAVE_TIME должно быть 8:00:00 + 8:00:00 = 16:00:00

Разве это возможно изменить? Может ли кто-нибудь помочь мне, как это сделать? заранее благодарю вас за любую помощь или совет.

Что я уже пробовал:

Я уже пробовал с этим запросом:

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;

Но, к сожалению, я понятия не имею, как это изменить.

---