Calculate Total working hour from daily attendance sheet with mysql

• zendnwar
• September 12, 2013
• SQL
• 0 Comments

SELECT TIMESTAMPDIFF(HOUR,
MIN(CASE WHEN inandout = ‘in’ THEN dateandtime END),
MAX(CASE WHEN inandout = ‘exit’ THEN dateandtime END))-1 AS HoursWorked,
employeeid, employeename,inandout,department,  DATE(dateandtime)
FROM attendance
WHERE employeeid = 575
AND dateandtime BETWEEN ‘2013-08-01’ AND ‘2013-08-23’
GROUP BY employeeid, DATE(dateandtime)

Create table with below code

CREATE TABLE `attendance` (
`attid` double DEFAULT NULL,
`employeeid` double DEFAULT NULL,
`employeename` varchar(300) DEFAULT NULL,
`dateandtime` datetime DEFAULT NULL,
`doorsname` varchar(300) DEFAULT NULL,
`inandout` varchar(300) DEFAULT NULL,
`department` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1