Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Summarise Leave Time Proportionally and Correctly in MySql

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 438
    Answer it

    In mysql database i created "leave" table:

     

    CREATE TABLE `leave` ( 
    `ID_LEAVE` int(11) NOT NULL,
    `ID_WORKER` int(11) NOT NULL,
    `BEGIN_DATE` datetime DEFAULT NULL,
    `END_DATE` datetime DEFAULT NULL
    )
    
    INSERT INTO `leave` 
    (`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
    (3, 26, '2019-03-20 07:00:00', '2019-03-21 15:00:00'),
    (4, 22, '2019-03-20 07:00:00', '2019-03-20 15:00:00');

     

    "Workers" table:

     

    CREATE TABLE `workers` (
    `ID_WORKER` int(11) NOT NULL,
    `FNAME` varchar(20) NOT NULL,
    `LNAME` varchar(20) NOT NULL
    )
    
    INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
    (22, 'ALAN', 'FAST'),
    (23, 'LEON', 'SPEED'),
    (24, 'ADAM', 'GREEN'),
    (25, 'DAVID', 'BUCS'),
    (26, 'JACK', 'FAR'),
    (27, 'ADAM', 'GAX'),
    (28, 'ANDREW', 'WORM');

     

     "Orders" table:

     

    CREATE TABLE `orders` (
    `ID_ORDER` int(11) NOT NULL,
    `DESC_ORDER` varchar(50) NOT NULL,
    `NUMBER_ORDER` varchar(30) NOT NULL
    )
    
    INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
    (19, 'TEST', 'TEST'),
    (20, 'TEST2', 'TEST2'),
    (21, 'TEST3', 'TEST3'),
    (22, 'TEST4', 'TEST4');

     

    "Order_status" table (I'm sorry for that's not in order):

     

    CREATE TABLE `order_status` (
    `ID_STATUS` int(11) NOT NULL,
    `ID_ORDER` int(11) NOT NULL,
    `ID_WORKER` int(11) NOT NULL,
    `BEGIN_DATE` datetime NOT NULL,
    `END_DATE` datetime NOT NULL,
    `ORDER_DONE` tinyint(1) DEFAULT NULL
    ) 
    
    INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`, `ORDER_DONE`) VALUES
    (30, 19, 26, '2019-03-18 06:40:21', '2019-03-18 15:22:32', 'NO'),
    (31, 19, 25, '2019-03-18 06:51:23', '2019-03-18 15:11:10', 'NO'),
    (32, 19, 26, '2019-03-20 06:23:32', '2019-03-20 15:33:11', 'NO'),
    (33, 19, 25, '2019-03-20 06:44:42', '2019-03-20 15:16:22', 'NO'),
    (34, 19, 26, '2019-03-22 06:44:32', '2019-03-22 11:44:54', 'YES'),
    (35, 19, 25, '2019-03-22 06:59:22', '2019-03-22 12:33:33', 'YES'),
    (42, 20, 22, '2019-03-18 06:44:23', '2019-03-18 15:11:23', 'NO'),
    (43, 20, 23, '2019-03-18 06:50:45', '2019-03-18 15:15:44', 'NO'),
    (44, 20, 22, '2019-03-19 06:40:20', '2019-03-19 15:23:30', 'NO'),
    (45, 20, 22, '2019-03-21 06:55:45', '2019-03-21 16:03:23', 'NO'),
    (46, 20, 22, '2019-03-22 06:55:45', '2019-03-22 13:23:44', 'YES'),
    (47, 20, 23, '2019-03-19 06:45:23', '2019-03-19 15:33:23', 'NO'),
    (48, 20, 23, '2019-03-20 06:45:23', '2019-03-20 15:22:23', 'NO'),
    (49, 20, 23, '2019-03-21 06:50:33', '2019-03-21 15:33:11', 'NO'),
    (50, 20, 23, '2019-03-22 06:44:23', '2019-03-22 12:22:44', 'YES'),
    (51, 21, 24, '2019-03-18 06:46:55', '2019-03-18 15:23:22', 'NO'),
    (52, 21, 24, '2019-03-19 06:55:33', '2019-03-19 15:23:33', 'NO'),
    (53, 21, 24, '2019-03-20 06:50:59', '2019-03-20 15:44:02', 'NO'),
    (54, 21, 24, '2019-03-21 06:44:33', '2019-03-21 15:23:33', 'YES'),
    (55, 21, 24, '2019-03-22 06:49:33', '2019-03-22 11:22:32', 'YES'),
    (56, 22, 27, '2019-03-18 06:50:12', '2019-03-18 15:22:15', 'NO'),
    (57, 22, 28, '2019-03-18 06:44:12', '2019-03-18 15:33:44', 'NO'),
    (58, 22, 27, '2019-03-19 06:53:22', '2019-03-19 15:22:33', 'NO'),
    (59, 22, 28, '2019-03-19 06:33:22', '2019-03-19 15:15:22', 'NO'),
    (60, 22, 28, '2019-03-21 06:33:22', '2019-03-21 15:01:56', 'NO'),
    (61, 22, 27, '2019-03-22 06:33:22', '2019-03-22 11:05:33', 'YES'),
    (62, 22, 27, '2019-03-21 06:45:22', '2019-03-21 15:33:33', 'NO'),
    (63, 22, 28, '2019-03-22 06:51:33', '2019-03-22 10:35:55', 'YES'),
    (64, 19, 25, '2019-03-19 06:50:32', '2019-03-19 15:33:44', 'NO'),
    (65, 19, 26, '2019-03-19 06:44:50', '2019-03-19 15:22:33', 'NO'),
    (66, 19, 25, '2019-03-21 06:50:50', '2019-03-21 15:33:33', 'NO');
    (67, 22, 27, '2019-03-20 06:51:32', '2019-03-20 15:20:33', 'NO');

     

    What i've done:

     

    I can to sumarize "Order time" of each other workers. 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'
    FROM workers
    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 | 
    +---------+---------+---------------+------------+------------+
    |  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |
    +---------+---------+---------------+------------+------------+
    |  JACK   |  FAR    | TEST          | TEST       | 21:19:18   |
    +---------+---------+---------------+------------+------------+

     

    I've analyzed 2 days (20.03.2019 and 21.03.2019). Photos are in below:

     

     

    Now i need mysql query that sumarize leave time correctly and PROPORTIONALLY to pattern described in below:

     

    for example for leave time where ID_WORKER = 26 (he has order "TEST")

     

    In mysql database i created "leave" table:

     

    CREATE TABLE `leave` ( 
    `ID_LEAVE` int(11) NOT NULL,
    `ID_WORKER` int(11) NOT NULL,
    `BEGIN_DATE` datetime DEFAULT NULL,
    `END_DATE` datetime DEFAULT NULL
    )
    
    INSERT INTO `leave` 
    (`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
    (3, 26, '2019-03-20 07:00:00', '2019-03-21 15:00:00'),
    (4, 22, '2019-03-20 07:00:00', '2019-03-20 15:00:00');

     

    "Workers" table:

     

    CREATE TABLE `workers` (
    `ID_WORKER` int(11) NOT NULL,
    `FNAME` varchar(20) NOT NULL,
    `LNAME` varchar(20) NOT NULL
    )
    
    INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
    (22, 'ALAN', 'FAST'),
    (23, 'LEON', 'SPEED'),
    (24, 'ADAM', 'GREEN'),
    (25, 'DAVID', 'BUCS'),
    (26, 'JACK', 'FAR'),
    (27, 'ADAM', 'GAX'),
    (28, 'ANDREW', 'WORM');

     

    "Orders" table:

     

    CREATE TABLE `orders` (
    `ID_ORDER` int(11) NOT NULL,
    `DESC_ORDER` varchar(50) NOT NULL,
    `NUMBER_ORDER` varchar(30) NOT NULL
    )
    
    INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
    (19, 'TEST', 'TEST'),
    (20, 'TEST2', 'TEST2'),
    (21, 'TEST3', 'TEST3'),
    (22, 'TEST4', 'TEST4');

    "Order_status" table (I'm sorry for that's not in order):

    CREATE TABLE `order_status` (
    `ID_STATUS` int(11) NOT NULL,
    `ID_ORDER` int(11) NOT NULL,
    `ID_WORKER` int(11) NOT NULL,
    `BEGIN_DATE` datetime NOT NULL,
    `END_DATE` datetime NOT NULL,
    `ORDER_DONE` tinyint(1) DEFAULT NULL
    ) 
    
    INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`, `ORDER_DONE`) VALUES
    (30, 19, 26, '2019-03-18 06:40:21', '2019-03-18 15:22:32', 'NO'),
    (31, 19, 25, '2019-03-18 06:51:23', '2019-03-18 15:11:10', 'NO'),
    (32, 19, 26, '2019-03-20 06:23:32', '2019-03-20 15:33:11', 'NO'),
    (33, 19, 25, '2019-03-20 06:44:42', '2019-03-20 15:16:22', 'NO'),
    (34, 19, 26, '2019-03-22 06:44:32', '2019-03-22 11:44:54', 'YES'),
    (35, 19, 25, '2019-03-22 06:59:22', '2019-03-22 12:33:33', 'YES'),
    (42, 20, 22, '2019-03-18 06:44:23', '2019-03-18 15:11:23', 'NO'),
    (43, 20, 23, '2019-03-18 06:50:45', '2019-03-18 15:15:44', 'NO'),
    (44, 20, 22, '2019-03-19 06:40:20', '2019-03-19 15:23:30', 'NO'),
    (45, 20, 22, '2019-03-21 06:55:45', '2019-03-21 16:03:23', 'NO'),
    (46, 20, 22, '2019-03-22 06:55:45', '2019-03-22 13:23:44', 'YES'),
    (47, 20, 23, '2019-03-19 06:45:23', '2019-03-19 15:33:23', 'NO'),
    (48, 20, 23, '2019-03-20 06:45:23', '2019-03-20 15:22:23', 'NO'),
    (49, 20, 23, '2019-03-21 06:50:33', '2019-03-21 15:33:11', 'NO'),
    (50, 20, 23, '2019-03-22 06:44:23', '2019-03-22 12:22:44', 'YES'),
    (51, 21, 24, '2019-03-18 06:46:55', '2019-03-18 15:23:22', 'NO'),
    (52, 21, 24, '2019-03-19 06:55:33', '2019-03-19 15:23:33', 'NO'),
    (53, 21, 24, '2019-03-20 06:50:59', '2019-03-20 15:44:02', 'NO'),
    (54, 21, 24, '2019-03-21 06:44:33', '2019-03-21 15:23:33', 'YES'),
    (55, 21, 24, '2019-03-22 06:49:33', '2019-03-22 11:22:32', 'YES'),
    (56, 22, 27, '2019-03-18 06:50:12', '2019-03-18 15:22:15', 'NO'),
    (57, 22, 28, '2019-03-18 06:44:12', '2019-03-18 15:33:44', 'NO'),
    (58, 22, 27, '2019-03-19 06:53:22', '2019-03-19 15:22:33', 'NO'),
    (59, 22, 28, '2019-03-19 06:33:22', '2019-03-19 15:15:22', 'NO'),
    (60, 22, 28, '2019-03-21 06:33:22', '2019-03-21 15:01:56', 'NO'),
    (61, 22, 27, '2019-03-22 06:33:22', '2019-03-22 11:05:33', 'YES'),
    (62, 22, 27, '2019-03-21 06:45:22', '2019-03-21 15:33:33', 'NO'),
    (63, 22, 28, '2019-03-22 06:51:33', '2019-03-22 10:35:55', 'YES'),
    (64, 19, 25, '2019-03-19 06:50:32', '2019-03-19 15:33:44', 'NO'),
    (65, 19, 26, '2019-03-19 06:44:50', '2019-03-19 15:22:33', 'NO'),
    (66, 19, 25, '2019-03-21 06:50:50', '2019-03-21 15:33:33', 'NO');
    (67, 22, 27, '2019-03-20 06:51:32', '2019-03-20 15:20:33', 'NO');

    What i've done:

    I can to sumarize "Order time" of each other workers. 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'
    FROM workers
    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 | 
    +---------+---------+---------------+------------+------------+
    |  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |
    +---------+---------+---------------+------------+------------+
    |  JACK   |  FAR    | TEST          | TEST       | 21:19:18   |
    +---------+---------+---------------+------------+------------+

    I've analyzed 2 days (20.03.2019 and 21.03.2019). Photos are in below:

    20.03.2019

    21.03.2019

    Now i need mysql query that sumarize leave time correctly and PROPORTIONALLY to pattern described in below:

    pattern to proportional leave time

    for example for leave time where ID_WORKER = 26 (he has order "TEST")

                        '08:31:40'                                      '08:31:40'
    (-------------------------------------------------) * 8:00:00 =  (--------------) * 8:00:00 = 2:00:16
     '08:31:40' + '08:37:00' + '08:53:03' + '08:29:01'                  '34:30:44'

    What I've tried: I've tried sum leave time by mysql query 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'
    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 I'm afraid that isn't enough because it only adds for example:

    22:30:21 + 8:00:00 = 30:30:21

    I wanna get that result:

    22:30:21 + 2:00:16 = 24:30:37

    CAUTION! If someone get's 2 or more days of leaves i cannot do that like this:

                        '08:31:40'                                      
    (-------------------------------------------------) * 16:00:00 
     '08:31:40' + '08:37:00' + '08:53:03' + '08:29:01'   

    I have to analyze every day like that example where i explained.

    I know that's more difficult question i cannot find any clue how to solve it. Can someone please what should i change or add. Thank you in advance for any help or advice.

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: