test attendance

CREATE TABLE tb_absensi(
  id_absen INT AUTO_INCREMENT PRIMARY KEY,
  id_karyawan INT,
  jam_absen DATETIME
 );
INSERT INTO tb_absensi VALUES
(1,1,'2017-05-12 05:45:00'),
(2,1,'2017-05-12 14:08:00'),
(3,2,'2017-05-12 13:46:00'),
(4,2,'2017-05-12 22:16:00'),
(5,3,'2017-05-12 22:08:00'),
(6,3,'2017-05-13 06:08:00');


 CREATE TABLE tb_shift(
   id_shift INT AUTO_INCREMENT PRIMARY KEY,
   nama_shift VARCHAR(10),
   start_shift TIME,
   end_shift TIME
 );

INSERT INTO tb_shift VALUES
(1,'SHIFT 1','06:00:00','13:59:00'),
(2,'SHIFT 2','14:00:00','21:59:00'),
(3,'SHIFT 3','22:00:00','05:59:00');

 CREATE TABLE tb_set_shift(
   id_set INT AUTO_INCREMENT PRIMARY KEY,
   id_kry INT,
   id_shift_nya INT,
   tgl_set_shift DATE
 );

INSERT INTO tb_set_shift VALUES
(1,1,1,'2017-05-12'),
(2,2,2,'2017-05-12'),
(3,3,3,'2017-05-12');

SELECT
a.id_karyawan,
IF(c.start_shift<c.end_shift,MIN(a.jam_absen),MAX(a.jam_absen)) AS mulai,
IF(c.start_shift<c.end_shift,MAX(a.jam_absen),MIN(a.jam_absen)) AS selesai,
IF(c.start_shift<c.end_shift,MAX(d.jam_absen),MIN(d.jam_absen)) AS selesai2,
d.jam_absen
FROM
tb_absensi a
LEFT JOIN tb_absensi d ON (a.id_karyawan=d.id_karyawan AND DATE(a.jam_absen)=SUBDATE(DATE(d.jam_absen), INTERVAL 1 DAY))
JOIN tb_set_shift b ON (a.id_karyawan=b.id_kry AND DATE(a.jam_absen)=DATE(b.tgl_set_shift))
JOIN tb_shift c ON (b.id_shift_nya=c.id_shift)
GROUP BY a.id_karyawan