How to sumarize correctly as “leave time” + “total time” - mysql

Question

3.00/5 (2 votes)

See more:

In mysql database i've created "leave" table:

+--------+---------+---------+-------------+---------+-------------------------------+
|ID_LEAVE|ID_WORKER| FNAME   | LNAME   | BEGIN_DATE          | END_DATE              | 
+--------+---------+---------+---------+------------+--------------------+-----------
| 1      |   1     | DAVID   | BUCS    |2019-03-19 07:00:00  |2019-03-19 15:00:00    | 
+--------+---------+---------+----------------------+--------------------+----------- 
| 2      |   2     | MARK    | GREEN   |2019-03-21 07:00:00  |2019-03-21 15:00:00    |
+--------+---------+---------+----------------------+--------------------------------+

"Workers" table:

+----------+---------+---------+
|ID_WORKER |  FNAME  | LNAME   |
+----------+---------+----------
| 1        |  DAVID  |  BUCS   |
+----------+---------+----------
| 2        |  MARK   |  GREEN  |
+----------+---------+---------+

"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. 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', 
       SEC_TO_TIME(SUM(leaveAgg.ltime)) AS 'LEAVE TIME' 
FROM   workers 
INNER JOIN (
SELECT leave.id_worker, SUM((Time_to_sec(leave.end_date) - 
                       Time_to_sec(leave.begin_date))) AS ltime
FROM leave
GROUP BY leave.id_worker
) leaveAgg
               ON leaveAgg.id_worker = workers.id_worker
       INNER 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 after that command i get:

+---------+---------+---------------+------------+------------+--------------+
 |  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME   |
 +---------+---------+---------------+------------+------------+--------------+
 |  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |   8:00:00    |
 +---------+---------+---------------+------------+------------+--------------+
 |  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   8:00:00    |
 +---------+---------+---------------+------------+------------+--------------+

what i'd like to do next:

What i'd like to do next: I'm trying sumarize all of them, i mean: TOTAL TIME + LEAVE_TIME for each Worker. For example:

 +---------+---------+---------------+------------+------------+-------------+----------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME   | SUM_TIME |
+---------+---------+---------------+------------+------------+--------------+----------+
|  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |   8:00:00    | 30:30:21 |
+---------+---------+---------------+------------+------------+--------------+----------+
|  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   8:00:00    | 29:19:18 | 
+---------+---------+---------------+------------+------------+--------------+----------+

I have no clue how to solve it. Has someone idea how to solve it? Any ideas. Thx very much for any help!

What I have tried:

i've tried add to at the top query:

SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';

But i know that's a bad idea.

Posted 21-Mar-19 9:27am

Member 10696161

Updated 22-Mar-19 0:44am

Add a Solution

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)

CHill60 · Accepted Answer · 2019-03-22T00:44:00

1. Using SEC_TO_TIME, TIME_TO_SEC seems like overkill. Why not just use TIMESTAMPDIFF[^]

2. You have a poor db design - there is no need to repeat the workers forename and surname in the leave table - you have that information on the workers table and you have a link to that table in leave.ID_WORKER

3. I can't see any reason for all of those sub-queries (and it wouldn't compile for me) Something like the following is far simpler, and easier to follow

SQL

select W.fname, W.lname, NUMBER_ORDER, DESC_ORDER
   ,SUM(TIMESTAMPDIFF(HOUR, os.begin_date, os.end_date)) as hoursWorkedOnOrders
   ,SUM(TIMESTAMPDIFF(HOUR, l.BEGIN_DATE, L.END_DATE)) as leavetime
   ,SUM(TIMESTAMPDIFF(HOUR, os.begin_date, os.end_date)) + SUM(TIMESTAMPDIFF(HOUR, l.BEGIN_DATE, L.END_DATE)) as totaltime
from #workers W
INNER JOIN #order_status OS ON W.id_worker = OS.id_worker
INNER JOIN #orders O ON OS.id_order = O.id_order
LEFT OUTER JOIN #leave L ON L.id_worker = W.id_worker
GROUP BY W.fname, W.lname, NUMBER_ORDER, DESC_ORDER

Edit - as an aside, I commend you for providing your table schemas, sample data, attempted code and expected results which is why I am upvoting your question. If you want to make it even easier for us to help you, avoid drawing tables with your data and just give us some simple ddl to reproduce the problem. To explain what I mean here is the sample data I used to get the above solution:

SQL

create table #leave(ID_LEAVE int identity(1,1),ID_WORKER int, FNAME varchar(30), LNAME varchar(30), BEGIN_DATE datetime, END_DATE datetime)
insert into #leave(ID_WORKER, FNAME, LNAME, BEGIN_DATE, END_DATE) values
(1, 'DAVID', 'BUCS','2019-03-19 07:00:00','2019-03-19 15:00:00'), 
(2, 'MARK', 'GREEN','2019-03-21 07:00:00','2019-03-21 15:00:00')
select * from 

create table #workers(ID_WORKER int identity(1,1),  FNAME varchar(30)  , LNAME varchar(30))
insert into #workers (FNAME  , LNAME) values
('DAVID',  'BUCS'),
('MARK',  'GREEN')

create table #orders (ID_ORDER int,  DESC_ORDER varchar(30), NUMBER_ORDER varchar(30))
insert into #orders (ID_ORDER  ,  DESC_ORDER  , NUMBER_ORDER) values
(20,  'TEST',  'TEST')

create table #order_status(Id_status int identity(30,1),ID_WORKER int, ID_ORDER int, BEGIN_DATE datetime, END_DATE datetime, ORDER_DONE varchar(3))
insert into #order_status (ID_WORKER, ID_ORDER, BEGIN_DATE, END_DATE, ORDER_DONE) values
(1,    20   ,'2019-03-18 06:50:35'  ,'2019-03-18 15:21:32',  'NO' )       ,
(1,    20   ,'2019-03-20 06:44:12'  ,'2019-03-20 15:11:23',  'NO' )       ,
(1,    20   ,'2019-03-22 06:50:20'  ,'2019-03-22 12:22:33',  'YES' )      ,
(2,    20   ,'2019-03-18 06:45:11'  ,'2019-03-18 15:14:45',  'NO'  )      ,
(2,    20   ,'2019-03-20 06:50:22'  ,'2019-03-20 15:10:32',  'NO'  )      ,
(2,    20   ,'2019-03-22 06:54:11'  ,'2019-03-22 11:23:45',  'YES' )