CODEWITHSUNDEEP
mysql
genpet
genpet

Reputation: 453

MYSQL timecard summary

Hi I have the below timecard structure on our system.

enter image description here

From the above timecard I need to generate a report that compares start from time_in AND end from time_out and group it by emp_id.

enter image description here

Any hint on how can I display it like the above would be great.

Upvotes: 1

Views: 220

Answers (1)

tpolyak
tpolyak

Reputation: 1239

The following query does the job. 

SELECT date, emp_id, 
    MAX(IF(event='t_in',time_in - start, 0)) AS 't1_in_diff',
    MAX(IF(event='t_out',time_out - end, 0)) AS 't1_out_diff',
    MAX(IF(event='b1_in',time_in - start, 0)) AS 'b1_in_diff',
    MAX(IF(event='b1_out',time_out - end, 0)) AS 'b1_out_diff',
FROM timecard 
GROUP BY date, emp_id;

If results can be negative (time_in/time_out can be smaller than start/end), you should set 0 to a value that is always smaller than the difference.

Upvotes: 3

Related Questions