Late arrival and early query in mysql with case and if argument

Question

I work with staff schedules, and below are tables. Tech Support and Network have two shifts for employees: i.e. [ 8:00 AM to 2:00 PM ] and [ 1:00 PM to 7:00 PM ]

And the normal shift [ 9:00 AM to 6:00 PM ]

Now I'm trying to get a message that people are late and leave early. I have little logic on the server side of the code, but its work is damn slow. So I wanted to do this from a database.

What am I doing

I create a report, employees who are late and leave early

Algorithm

• To find the start time of the employee with the appropriate department .. and the corresponding shifts
• If the employee department is Software, make one shift, make a second shift in the afternoon
• From punchin time and exit time .. find employee shift.
• If the employee shift is under the first shift, use intime as 08:00:00 AM and outtime as 01:00:00 01:00:00 PM
• If the employee shift is under the second shift, use intime as 02:00:00 PM and outtime as 07:00:00 PM
• If an employee change occurs under normal shift, use intime as 08:55:00 AM and outtime as 06:00:00 PM

Then find, now find its login_time and log

Tables

Employees

 emp_id | emp_name | emp_dept -------------------------------------- 1 | Billy J | 1 2 | Sarah k | 2 3 | Takashi M | 3 4 | Matsuzaka | 2 

Department

 dept_id | dept_name -------------------------- 1 | Software 2 | Tech Support 3 | Network 

employee_login

 emp_id | login_time | logout_time ---------------------------------------------------------- 1 | 2013-02-18 19:10:42 | 2013-02-18 21:27:37 2 | 2013-02-18 19:38:59 | 2013-02-18 22:46:14 3 | 2013-02-18 15:13:53 | 2013-01-01 18:26:39 4 | 2013-01-01 08:41:40 | 2013-01-01 016:41:40 

SQL Query, I tried and is a bit incomplete ...

 SELECT e.emp_id, e.emp_name, d.dept_name, CASE d.dept_name WHEN d.dept_name IN ('Software') THEN @intime := '08:55:00 AM' ELSE @intime := '02:00:00 PM' END AS `StartingTime`, @entrytime := DATE_FORMAT(el.login_time, '%r%') AS `Entered into Office` TIMEDIFF(@entryTime,@intime) `difference`, IF(TIMEDIFF(@entryTime,@intime)<'00:00:00',NULL,TIMEDIFF(@entryTime,@intime)) AS `Delay` FROM employees e INNER JOIN department d ON d.dept_id = e.emp_dept INNER JOIN employee_login el ON el.emp_id = e.emp_id WHERE DATE_FORMAT(el.login_time, '%Y-%m-%d') BETWEEN '2013-06-01' AND '2013-06-26' ORDER BY el.login_time DESC; 

Answer 1

Below is the code for this new simplified CASE Fiddle . Here is the old CASE Fiddle .
- Some columns are really not needed, and you can eliminate them if you want.
- Pay attention! that I had to use CASE in every column that needed to be set in accordance with the conditions for the departments. Also, please note that I used the timings you asked when begging your question. If you want to change them, do not forget to do it in each CASE statement, otherwise you will not get the desired results.

 SELECT e.emp_id, d.dept_name, e.emp_name, CASE d.dept_name WHEN 'Tech Support' THEN '08:00:00' WHEN 'Network' THEN '13:00:00' ELSE '09:00:00' END AS `StartingTime`, CASE d.dept_name WHEN 'Tech Support' THEN '14:00:00' WHEN 'Network' THEN '19:00:00' ELSE '18:00:00' END AS `EndingTime`, TIME_FORMAT(el.login_time, '%T') AS `Entered_into_Office`, TIME_FORMAT(el.logout_time, '%T') AS `Left_from_Office`, CASE d.dept_name WHEN 'Tech Support' THEN TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('08:00:00', '%T')), '%T') WHEN 'Network' THEN TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('13:00:00', '%T')), '%T') ELSE TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('09:00:00', '%T')), '%T') END AS `Time_in_diff`, CASE d.dept_name WHEN 'Tech Support' THEN TIME_FORMAT(TIMEDIFF(TIME_FORMAT('14:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T') WHEN 'Network' THEN TIME_FORMAT(TIMEDIFF(TIME_FORMAT('19:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T') ELSE TIME_FORMAT(TIMEDIFF(TIME_FORMAT('18:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T') END AS `Time_out_diff`, CASE d.dept_name WHEN 'Tech Support' THEN TIME_FORMAT(SEC_TO_TIME( TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('08:00:00', '%T')), '%T')) +TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('14:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T') WHEN 'Network' THEN TIME_FORMAT(SEC_TO_TIME( TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('13:00:00', '%T')), '%T')) +TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('19:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T') ELSE TIME_FORMAT(SEC_TO_TIME( TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('09:00:00', '%T')), '%T')) +TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('18:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T') END AS `Total_time_diff` FROM employees e INNER JOIN department d ON d.dept_id = e.emp_dept INNER JOIN employee_login el ON el.emp_id = e.emp_id WHERE DATE_FORMAT(el.login_time, '%Y-%m-%d') BETWEEN '2012-01-01' AND '2013-12-31' HAVING Total_time_diff > 0; 

Below are my previous solutions:
In this SQLFiddle, I made 4 queries for your area. This is a request for one department. I did not use the CASE condition, but it works. One for each time shift and one data reception for all time sheets. For later, I had to remove this line: ORDER BY Time_in_diff DESC; for UNION success to be successful. Let me know if this helps! Here is a solution with prepared instructions .