CODEWITHSUNDEEP
phpmysql
Tamás Pap
Tamás Pap

Reputation: 18303

Php/MySQL: Hours reported, hourly rate

I have the following tables (simplified):

   hours               hour_rates
 - user_id            - user_id
 - date               - date
 - hours              - hourly_rate

Hours table example:

1 - 2012-03-19 - 8

This means that user with id=1, at 2012-03-19 worked 8 hours in total.

The hourly rate for a person can change in time, so I have the second table:

hour_rates table Example

1 - 2011-12-01 - 20
1 - 2011-12-20 - 25

So for user with id=1, we set a hourly rate of 20$ at 2011-12-01. We changed his hourly rate at 2011-12-20, to 25$.

What I want is, to calculate how much I have to pay for a given user (ex. id=1) for a given period (ex. 2012-01-01 -> 2012-02-01).

Can I calculate this simply mysql side? If not, how to do it in an efficient way?

Upvotes: 1

Views: 2664

Answers (4)

piotrm
piotrm

Reputation: 12356

This query should work:

SELECT SUM( h.hours*COALESCE(hr.hourly_rate,0) ) AS salary
FROM hours h
LEFT JOIN hour_rates hr
ON h.user_id = hr.user_id 
AND  hr.date = ( SELECT MAX(hd.date) FROM hour_rates hd
                 WHERE hd.user_id = h.user_id AND hd.date <= h.date )
WHERE h.user_id = 1
AND h.date BETWEEN '2012-01-01' AND '2012-04-01'

The coalesce part is there just in case the hourly pay is not defined for a given user and date, you can put a default rate there instead of 0. Also make sure that every pair (user_id,date) in your hour_rates table is unique.

Upvotes: 1

Chris
Chris

Reputation: 3846

EDIT: So, sorry but what do you need 2 tables for that? They are totally equal, if every day can have its own hourly_rate, you only need to store it like this:

hours: user_id, date, hours, hourly_rate

e.g. 1 | 2012-03-19 | 8 | 25

You use

 SELECT user_id, date, hours, hourly_rate FROM hours WHERE (user_id=$var_of_user_id AND date ...)

and then multiply for each row hours*hourly_rate and add it to $sum, e.g.

while {...

$sum=$sum+($row['hours']*$row['hourly_rate']);

}

what does the hourly_rate depend on? probably you don't want to have the field date in the hour_rates table.

if hourly_rate is different for each job, you want to have only one table with user_id, start_date, end_date (or hours worked) and hourly_rate.

if hourly_rate depends only on user_id, then you want to have two tables:

hours: user_id, start_date, end_date (or hours worked)

hourly_rate: user_id, hourly_rate

and join the tables on user_id. If you already have a table users, you could store the hourly_rate there too, in the second case.

Then use php to simply multiply the hours worked with the hourly rate where user_id... etc.

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

Can I calculate this simply mysql side?

Yes, this is the SQL

select sum(outer_h.hours * 
  (select inner_hr.hourly_rate 
    from hour_rates inner_hr
    where inner_hr.user_id = outer_h.user_id
    and inner_hr.date >= outer_h.date
    order by inner_hr.date asc
    limit 1)
) as pay
  from hours outer_h
  where outer_h.user_id = :user_id --here you will set the user id parameter
  and outer_h.date between STR_TO_DATE('01,1,2012','%d,%m,%Y') and STR_TO_DATE('01,1,2011','%d,%m,%Y')

Upvotes: 1

ab_dev86
ab_dev86

Reputation: 1982

In hour_rates table you should have two dates: start_date and end_date this means from start_date to end_date the emplyer has been paid x$ per hour.

Then use the same query proposed by bpgergo modified like this: 

   select sum(h.hours * hr.hourly_rate) as pay
   from hours h, hour_rates hr
   where h.user_id = :user_id --here you will set the user id parameter
   and h.user_id = hr.user_id and (h.date BETWEEN hr.start_date AND hr.end_date 
   and h.date between STR_TO_DATE('01,1,2012','%d,%m,%Y') and STR_TO_DATE('01,1,2011','%d,%m,%Y')

Upvotes: 1

Related Questions