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.shift_id = 12 AND 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.shift_id = 12 AND ats2.day_index = (0 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1)) AS horario,
	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 CAST(att3.in_time AS datetime) FROM att_timeinterval att3 WHERE att3.id = (SELECT time_interval_id FROM att_shiftdetail ats3 WHERE ats4.shift_id = 12 AND ats3.day_index = (7 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1),
        (SELECT CAST(att4.in_time AS datetime) FROM att_timeinterval att4 WHERE att4.id = (SELECT time_interval_id FROM att_shiftdetail ats4 WHERE ats4.shift_id = 12 AND ats4.day_index = (0 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1)) AS 'FixedEntrance',
	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 CAST(addtime((floor(att5.duration / 60)*10000 + (att5.duration % 60*100)), time(att5.in_time)) AS datetime) FROM att_timeinterval att5 WHERE att5.id = (SELECT time_interval_id FROM att_shiftdetail ats5 WHERE ats5.shift_id = 12 AND ats5.day_index = (7 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1),
        (SELECT CAST(addtime((floor(att6.duration / 60)*10000 + (att6.duration % 60*100)), time(att6.in_time)) AS datetime) FROM att_timeinterval att6 WHERE att6.id = (SELECT time_interval_id FROM att_shiftdetail ats6 WHERE ats6.shift_id = 12 AND ats6.day_index = (0 + DAYOFWEEK(iclock_transaction.punch_time) - 1) LIMIT 1) LIMIT 1)) 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;