CODEWITHSUNDEEP
phpmysql
csteel
csteel

Reputation: 393

Figure out overtime hours based on a punch in, punch out, puch lunch

This is my set of data:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL PRIMARY KEY
,user_id INT NOT NULL
 ,payroll_id INT NOT NULL
,type CHAR(3) NOT NULL
,time INT NOT NULL
,created INT NOT NULL 
,modified INT NULL 
 ,site_id INT NOT NULL
,is_lunch TINYINT NOT NULL DEFAULT 0
);

INSERT INTO my_table VALUES 
(242144 ,1289 ,0 ,'in'  ,1389020986 ,1389020986 ,1389020986 ,1000 ,0),
(242679 ,1289 ,0 ,'out' ,1389049440 ,1389135896 ,1389194110 ,1000 ,0),
(242777 ,1289 ,0 ,'in'  ,1389106200 ,1389194089 ,1389194137 ,1000 ,0),
(242778 ,1289 ,0 ,'out' ,1389136200 ,1389194092 ,1389194153 ,1000 ,0),
(242779 ,1289 ,0 ,'in'  ,1389194095 ,1389194095 ,1389194095 ,1000 ,0),
(242976 ,1289 ,0 ,'out' ,1389221878 ,1389221878 ,1389221878 ,1000 ,0),
(243062 ,1289 ,0 ,'in'  ,1389280527 ,1389280527 ,1389280527 ,1000 ,0),
(243070 ,1289 ,0 ,'out' ,1389280683 ,1389280683 ,1389280683 ,1000 ,0),
(243071 ,1289 ,0 ,'in'  ,1389280686 ,1389280686 ,1389280686 ,1000 ,0),
(243278 ,1289 ,0 ,'out' ,1389309710 ,1389309710 ,1389309710 ,1000 ,0),
(243361 ,1289 ,0 ,'in'  ,1389365719 ,1389365719 ,1389365719 ,1000 ,0),
(243573 ,1289 ,0 ,'out' ,1389395108 ,1389395108 ,1389395108 ,1000 ,0),
(243700 ,1289 ,0 ,'in'  ,1389626136 ,1389626136 ,1389626136 ,1000 ,0),
(243886 ,1289 ,0 ,'out' ,1389654144 ,1389654144 ,1389654144 ,1000 ,0),
(243998 ,1289 ,0 ,'in'  ,1389713199 ,1389713199 ,1389713199 ,1000 ,0),
(244177 ,1289 ,0 ,'out' ,1389741915 ,1389741915 ,1389741915 ,1000 ,0),
(244254 ,1289 ,0 ,'in'  ,1389797075 ,1389797075 ,1389797075 ,1000 ,0),
(244461 ,1289 ,0 ,'out' ,1389827243 ,1389827243 ,1389827243 ,1000 ,0),
(244545 ,1289 ,0 ,'in'  ,1389883652 ,1389883652 ,1389883652 ,1000 ,0),
(244762 ,1289 ,0 ,'out' ,1389914226 ,1389914226 ,1389914226 ,1000 ,0),
(244830 ,1289 ,0 ,'in'  ,1389968788 ,1389968788 ,1389968788 ,1000 ,0),
(245025 ,1289 ,0 ,'out' ,1389996312 ,1389996312 ,1389996312 ,1000 ,0),
(245214 ,1289 ,0 ,'in'  ,1390232302 ,1390232302 ,1390232302 ,1000 ,0);

I would like to figure out the total number of hours worked per week and then subtract that number from 40 to get overtime hours. Broken down by week with week 1 normal hours, week 1 overtime hours, week 2 normal hours, week 2 overtime hours. All through MYSQL. Is it even possible with this data-set?

Time is the time the punch is made. So if your 'type' is 'in' your clock has started. The next 'out' denotes you punched out so you find how much 'time' happened between the 'in' and 'out'.

Upvotes: 0

Views: 560

Answers (1)

Fabien Warniez
Fabien Warniez

Reputation: 2741

You can SUM all the out, and then SUBSTRACT all the in, then group them by day. This is making the assumption that for each in, there is an out on the same day.

To group them by day, you need to use DATE functions to extract the year-month-day out of the timestamps.

Something like:

SELECT
    SUM(IF(type = 'in', -1 * time, time)),
    DATE_FORMAT(time, 'some format') AS day
FROM punch
GROUP BY day

For the format, see: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Upvotes: 1

Related Questions