SELECT DISTINCT
   personnel_employee.id AS 'EmployeeID',
   personnel_employee.emp_code AS 'CodeID',
   dept_name AS 'DepartmentName',
   personnel_employee.first_name AS 'Name',
   att_attshift.alias AS 'Schedule',
   IF((WEEK(iclock_transaction.punch_time, 2) - WEEK(iclock_transaction.punch_time - INTERVAL DAY(iclock_transaction.punch_time)-1 DAY, 2) + 1) % 2 = 0,
        SELECT att1.alias FROM att_timeinterval att1 WHERE att1.id = (SELECT time_interval_id FROM att_shiftdetail ats WHERE ats.day_index = (7 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1,
        SELECT att2.alias FROM att_timeinterval att2 WHERE att2.id = (SELECT time_interval_id FROM att_shiftdetail ats2 WHERE ats2.day_index = (0 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1) AS horario,
   CAST(att_timeinterval.in_time AS datetime) AS 'FixedEntrance',
   CAST(addtime((floor(duration / 60)*10000 + (duration % 60*100)), time(att_timeinterval.in_time)) AS datetime) AS 'FixedExit',
   CAST(iclock_transaction.punch_time AS date) AS 'CurrentDay',
   DATE_FORMAT(iclock_transaction.punch_time, '%H:%i:%s') AS 'Time1'
FROM
   att_shiftdetail
   INNER JOIN
      att_attshift
      ON att_attshift.id = att_shiftdetail.shift_id
   INNER JOIN
      att_timeinterval
      ON att_timeinterval.id = att_shiftdetail.time_interval_id
   INNER JOIN
      att_attschedule
      ON att_attschedule.shift_id = att_attshift.id
   INNER JOIN
      personnel_employee
      ON att_attschedule.employee_id = personnel_employee.id
   INNER JOIN
      iclock_transaction
      ON personnel_employee.emp_code = iclock_transaction.emp_code
   INNER JOIN
      personnel_department
      ON personnel_employee.department_id = personnel_department.id
WHERE
   personnel_employee.status <> 3
   AND CAST(iclock_transaction.punch_time AS date) BETWEEN '2018-05-11' AND '2018-05-28'
   AND personnel_department.id = '11'
   AND personnel_employee.emp_code = '101104'
   and dayname(iclock_transaction.punch_time) = elt(att_shiftdetail.day_index mod 7 + 1, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
   and elt(att_shiftdetail.day_index mod 7 + 1, '0', '1', '2', '3', '4', '5', '6') = att_shiftdetail.day_index
group by
   iclock_transaction.punch_time
ORDER BY
   personnel_employee.emp_code,
   iclock_transaction.punch_time;