CODEWITHSUNDEEP
mysqlsqlselect
Lupin
Lupin

Reputation: 81

Difficult SQL Statement (grouping and difference between two times)

I try to do a quite difficult sql select statement. Any hints are well appreciated :). Links etc. Here are my tables:

tbl_user
id     f_name       l_name    hourly_rate
1      Tom          Riddle    25
2      Frank        Smith     30
3      Lupin        Moon      35

tbl_entries
id     s_time   e_time   fk_user   fk_bank
1      08:00    10:00    1         1
2      11:05    11:50    1         2
3      12:00    18:00    2         1
4      13:00    15:00    2         1
5      10:00    12:00    3         2

tbl_bank
id     bank
1      a
2      b

I'd like to select and group them by tbl_bank. So my expected output should be:

id (tbl_user)     f_name     l_name       bank      total time (difference)   wage
1                 Tom        Riddle       a         02:00                     50
1                 Tom        Riddle       b         00:45                     18.75  
2                 Frank      Smith        a         08:00                     240
3                 Lupin      Moon         b         02:00                     70

I have something like this

SELECT
    id,
    f_name,
    l_name,
    hourly_rate
FROM
    tbl_user
    INNER JOIN tbl_entries ON tbl_user.id         = tbl_entries.fk_user
    INNER JOIN tbl_bank    ON tbl_entries.fk_bank = tbl_bank.id

which gives me all values but is obviously not grouped and without total time and .

Upvotes: 1

Views: 54

Answers (3)

Noel H
Noel H

Reputation: 1

I would recommend using a full datetime data type for the times for this for when the work shift passes midnight. But this should work.

SELECT tbl_user.id AS id (tbl_user), f_name, l_name, bank, hourly_rate, TIMEDIFF(e_time, s_time) AS total time (difference), (unix_timestamp(e_time) - unix_timestamp(s_time)) / 60 / 60 * hourly_rate AS wages FROM tbl_user INNER JOIN tbl_entries ON tbl_user.id = tbl_entries.fk_user INNER JOIN tbl_bank ON tbl_entries.fk_bank = tbl_bank.id GROUP BY tbl_user.id, bank;

Upvotes: 0

rkwee
rkwee

Reputation: 1

you could try using TIMESTAMPDIFF and go from there something like:

SELECT tu.id, tu.f_name, tu.l_name, tb.bank , TIMESTAMPDIFF(hour,CONCAT(CAST(CURDATE() AS INT),REPLACE(CONCAT(te.e_time,":00"),":","")),CONCAT(CAST(CURDATE() AS INT),REPLACE(CONCAT(te.s_time,":00"),":",""))) * tu.hourly_rate wage from tbl_entries te JOIN tbl_user tu ON (te.fk_users=tu.id) JOIN tbl_bank tb on (te.fk_bank=tb.id);

presume that your time is always current date (hence CURDATE() inside the query) and the SECONDS always start from 'hh:mm:00'

Upvotes: 0

Dai
Dai

Reputation: 155270

SELECT
    u.id,
    u.f_name,
    u.l_name,
    u.hourly_rate,
    ( SUM( TIMEDIFF( e.e_time, e.s_time ) / 60*60 )                 ) AS "total time (hours)",
    ( SUM( TIMEDIFF( e.e_time, e.s_time ) / 60*60 ) * u.hourly_rate ) AS wage
FROM
    tbl_user AS u
    INNER JOIN tbl_entries AS e ON u.id      = e.fk_user
    INNER JOIN tbl_bank    AS b ON e.fk_bank = b.id
GROUP BY
    u.id,
    b.id

Upvotes: 2

Related Questions