CODEWITHSUNDEEP
mysql
nenad007
nenad007

Reputation: 139

MySQL time calculation when

I have two tables, one (personal_work) where I record working time of the employee, another table where I store how much which employee earn per hour depending on the working time. A employee can have depending on the working time various hour rates.

Is it possible to get the result trough a query within MySQL?

Will the table work_our work as is or I must take take time_to is never == time_from from another record for the same employee?

personal_work

id | work_date  | time_from | time_to  | personal_id
 1 | 2017-01-01 | 16:00:00  | 22:00:00 | 3
 2 | 2017-01-01 | 11:00:00  | 12:00:00 | 6
 3 | 2017-01-01 | 15:00:00  | 02:00:00 | 10
 4 | 2017-01-01 | 20:00:00  | 00:00:00 | 12

work_hour

id | personal_id | valid_date_from | valid_date_to | time_from | time_to  | amount
 1 |           3 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 5.55
 2 |           3 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 7.77
 3 |           6 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 8.88
 4 |           6 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 5.55
 5 |          10 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 7.00
 6 |          10 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 10.00
 7 |          12 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 4.56
 8 |          12 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 7.89

result

personal_id | period_from         | period_to           | paid
          3 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 33.30
          6 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 8.88
         10 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 86.00
         12 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 21.57

Upvotes: 0

Views: 44

Answers (1)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

When you ignore the valid_date_from and valid_date_to, you can use this:

SELECT lo1.personal_id,

       ROUND(SUM((TIME_TO_SEC(TIMEDIFF(lo1.end_datetime, lo1.start_datetime))/3600 - 
       TIME_TO_SEC(IF(lo1.start_datetime < lo2.start_datetime,TIMEDIFF(lo2.start_datetime, lo1.start_datetime),0))/3600 -
       TIME_TO_SEC(IF(lo1.end_datetime > lo2.end_datetime,TIMEDIFF(lo1.end_datetime, lo2.end_datetime),0))/3600) * amount), 2) AS to_pay
FROM
    (SELECT personal_id,
            ADDTIME(work_date, time_from) AS start_datetime,
            ADDTIME(IF(time_to > time_from, work_date, work_date + INTERVAL 1 DAY), time_to) AS end_datetime
     FROM personal_work) AS lo1
JOIN
    (SELECT personal_id,
            ADDTIME(work_date, time_from) AS start_datetime,
            ADDTIME(IF(time_to > time_from, work_date, work_date + INTERVAL 1 DAY), time_to) AS end_datetime,
            amount
     FROM
         (SELECT DISTINCT work_date
          FROM personal_work
          UNION SELECT max(work_date) + interval 1 DAY
          FROM personal_work AS work_date) AS dates_table
     JOIN work_hour) AS lo2 ON lo1.personal_id = lo2.personal_id
AND (lo1.start_datetime BETWEEN lo2.start_datetime AND lo2.end_datetime
     OR lo1.end_datetime BETWEEN lo2.start_datetime AND lo2.end_datetime)
GROUP BY 1

Upvotes: 1

Related Questions