0

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.

I need a monthly report per employee that shows a List of all worked shifts and how much he earn.

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-02 | 18:00:00  | 01:00:00 | 3
 3 | 2017-01-03 | 15:00:00  | 02:00:00 | 3
 4 | 2017-01-01 | 20:00:00  | 00:00:00 | 4

work_hour

id | personal_id | valid_date_from | valid_date_to | time_from | time_to  | amount
 1 |           3 | 2013-01-01      | 2017-01-31    | 07:00:00  | 23:00:00 | 5.00
 2 |           3 | 2013-01-01      | 2017-01-31    | 23:00:00  | 07:00:00 | 7.00
 3 |           3 | 2017-02-01      |          null | 07:00:00  | 23:00:00 | 8.00
 4 |           3 | 2017-02-01      |          null | 23:00:00  | 07:00:00 | 9.00
 5 |           4 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 11.00
 6 |           4 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 15.00

needed result for personal_id = 3

 personal_id | worked_from         | worked_to           | paid
           3 | 2017-01-01 16:00:00 | 2017-02-01 22:00:00 | 30.00 (6x 5.00)
           3 | 2017-01-02 18:00:00 | 2017-02-02 01:00:00 | 39.00 (5x 5.00 + 2x 7.00)
           3 | 2017-01-03 15:00:00 | 2017-02-03 02:00:00 | 61.00 (8x 5.00 + 3x 7.00)

I was able to get the List of working shifts, but don't found a way to got the correct amount for working time and to respect if the employee had a shift where he got two different rates paid.

SELECT
  work_date,
  time_from,
  time_to
FROM
  personal_work
WHERE work_date BETWEEN '2017-01-01'
  AND '2017-01-31'
  AND personal_id = 3

Is there a elegant way to handle this in MySQL?

nenad007
  • 139
  • 8
  • I'm going to say no. I think you will need multiple queries and will also have to manipulate the data in which ever programming language you are using. Also, your data doesn't look right. personal_id 3 has two entries for 2017-01-01 and one of those has him starting in the afternoon and ending early that morning. – CptMisery Aug 30 '17 at 20:10
  • @CptMisery I have fixed the wrong entries. I think is possible, but don't figured it out yet. – nenad007 Aug 30 '17 at 20:30
  • I think your other correspondent is mistaken – Strawberry Aug 30 '17 at 23:09

1 Answers1

0

So, from your example I can assume that a single personnel has only one record in table personal_work per day. Also they have a fixed work rate per any given day. If that is true, then you can user the following query:

SELECT
  work_date,
  time_from,
  time_to,
  TIMESTAMPDIFF(HOUR, time_from, time_to) as work_duration,
  (amount * TIMESTAMPDIFF(HOUR, time_from, time_to)) as paid
FROM personal_work
INNER JION work_hour 
ON work_hour.personal_id = personal_work.personal_id
AND work_date BETWEEN valid_date_from and valid_date_to
WHERE work_date BETWEEN '2017-01-01'
  AND '2017-01-31'
  AND personal_id = 3
Lamar
  • 1,761
  • 4
  • 24
  • 50
  • aside of this that it don't work (time_from in field list is ambiguous) it don't solve the problem, as the employee can work twice a day, and he can have inside one shoft two different hourly reates. Thank you anyway. – nenad007 Aug 30 '17 at 20:35
  • First, I don't have the table structure to test the query, so yes you might find such errors, as you got. Second, if your shift has more than one hourly rate, then I strongly suggest dividing it into more than one shift, at least logically in your program. And finally, you really should write a good example in your sample data so that we can better understand your situation. – Lamar Aug 30 '17 at 20:39