absen

SELECT
    absen.pin,
    emp.employee_id,
    absen.nama_karyawan,
    posisi.`name` AS posisi,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-01' AND absen.tipe_scan_id = 1, absen.tanggal_scan, NULL ) AS masuk_01,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-01' AND absen.tipe_scan_id = 2, absen.tanggal_scan, NULL ) AS pulang_01,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-02' AND absen.tipe_scan_id = 1, absen.tanggal_scan, NULL ) AS masuk_02,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-02' AND absen.tipe_scan_id = 2, absen.tanggal_scan, NULL ) AS pulang_02,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-03' AND absen.tipe_scan_id = 1, absen.tanggal_scan, NULL ) AS masuk_03,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-03' AND absen.tipe_scan_id = 2, absen.tanggal_scan, NULL ) AS pulang_03,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-04' AND absen.tipe_scan_id = 1, absen.tanggal_scan, NULL ) AS masuk_04,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-04' AND absen.tipe_scan_id = 2, absen.tanggal_scan, NULL ) AS pulang_04,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-05' AND absen.tipe_scan_id = 1, absen.tanggal_scan, NULL ) AS masuk_05,
IF
    ( DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) = '2020-01-05' AND absen.tipe_scan_id = 2, absen.tanggal_scan, NULL ) AS pulang_05
--  SUM(
--  IF
--      (
--          DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) >= '2020-01-01'
--          AND DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) <= '2020-01-05'
--          AND absen.tipe_scan_id = 1,
--          1,
--          0
--      )
--  ) AS total_masuk
FROM
    0_absensi_karyawan AS absen
    JOIN 0_absensi_karyawan_batch_upload AS batch ON ( batch.id = absen.batch_id )
    JOIN 0_employee_master AS emp ON ( emp.pin_finger = absen.pin )
    JOIN 0_employee_position AS emp_pos ON ( emp_pos.employee_id = emp.employee_id )
    JOIN 0_position AS posisi ON ( posisi.id = emp_pos.position_id )
WHERE
    DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) >= '2020-01-01'
    AND DATE_FORMAT( absen.tanggal_scan, '%Y-%m-%d' ) <= '2020-01-05'
    AND emp.deleted_mark = 0
-- GROUP BY
--  emp.employee_id
ORDER BY
    absen.tipe_scan_id ASC