JOIN multiple table dengan operasi SUM beberapa kolom didalamnya – 2

Berawal dari tulisan saya yang berjudul “JOIN multiple table dengan operasi SUM beberapa kolom didalamnya”, ada pembaca yang menanyakan suatu kasus dengan prinsip yang hampir sama. Kasusnya adalah ingin membuat rekap data karyawan berupa total absensi shift, total lembur, total sakit, total cuti, total ijin. Ilustrasinya laporannya adalah sbb :

nama karyawan | total absen | total lembur | total sakit | total cuti | total ijin |
——————–|—————|—————-|————–|————|————|
karyawan A
karyawan B
dst

Ok sekarang akan saya kupas satu persatu apa yang akan diperlukan untuk membuat laporan tersebut. Harus diingat bahwa tidak setiap karyawan akan terisi semua rekapnya. Misal karyawan A mungkin dalam periode tertentu belum pernah cuti, artinya akan ada data kosong didalam rekapnya. Dengan melihat pola tersebut maka kolom yang cocok untuk dijadikan kunci-nya adalah kolom nama karyawan dan yang bisa menyelesaikan permasalahan tersebut dengan operasi LEFT JOIN dengan tabel nama karyawan sebagai tabel left yang akan dijoinkan dengan tabel yang lain yaitu tabel absen, tabel lembur, tabel sakit, tabel cuti dan tabel ijin. Itu artinya saya akan melakukan LEFT JOIN terhadap total 6 tabel!!! Fiuuhhh…….(untuk dasar-dasar penggunaan LEFT JOIN silahkan lihat tulisan saya yang pertama)

Ok kita mulai….

1. Tabel karyawan : tbl_karyawan

19-04-2014 21-18-16

2. Table lembur : tbl_absen_lembur

19-04-2014 21-21-40

3. Tabel absensi : tbl_absen_shift

19-04-2014 21-23-33

4. Tabel cuti : tbl_cuti

19-04-2014 21-24-38

5. Tabel sakit : tbl_sakit

19-04-2014 21-25-41

6. Tabel ijin : tbl_ijin

19-04-2014 21-26-49

Ok 6 tabel sudah dibuat, kemudian diisi dengan data. Dan tibalah kita pada pembuatan rekap laporan. Rencananya rekap ini bisa dipanggil berdasar periode tertentu. Perintah SQL-nya adalah sbb :

SELECT tbl_karyawan.id_karyawan, tbl_karyawan.nama_karyawan,
SUM(DATEDIFF(tbl_sakit.tgl_selesai,tbl_sakit.tgl_mulaisakit)) AS total_sakit, total_ijin, total_cuti,
total_absen, total_lembur
FROM tbl_karyawan
LEFT JOIN tbl_sakit ON tbl_karyawan.id_karyawan=tbl_sakit.id_karyawan
AND month(tbl_sakit.tgl_mulaisakit)='01' AND year(tbl_sakit.tgl_mulaisakit)='2014'


LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(DATEDIFF(tbl_ijin.tgl_selesai,tbl_ijin.tgl_mulaiijin)) AS total_ijin
FROM tbl_karyawan, tbl_ijin
WHERE tbl_karyawan.id_karyawan=tbl_ijin.id_karyawan
GROUP BY tbl_ijin.id_karyawan) AS ijin
ON tbl_karyawan.id_karyawan=ijin.id_karyawan

LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(DATEDIFF(tbl_cuti.tgl_selesai,tbl_cuti.tgl_mulaicuti)) AS total_cuti
FROM tbl_karyawan, tbl_cuti
WHERE tbl_karyawan.id_karyawan=tbl_cuti.id_karyawan
GROUP BY tbl_cuti.id_karyawan) AS cuti
ON tbl_karyawan.id_karyawan=cuti.id_karyawan

LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(HOUR(TIMEDIFF(tbl_absen_shift.waktu_selesai,tbl_absen_shift.waktu_mulaishift))) AS total_absen
FROM tbl_karyawan, tbl_absen_shift
WHERE tbl_karyawan.id_karyawan=tbl_absen_shift.id_karyawan
AND month(tbl_absen_shift.tgl_shift)='01' AND year(tbl_absen_shift.tgl_shift)='2014'
GROUP BY tbl_absen_shift.id_karyawan) AS absen
ON tbl_karyawan.id_karyawan=absen.id_karyawan


LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(HOUR(TIMEDIFF(tbl_absen_lembur.waktu_selesai,tbl_absen_lembur.waktu_mulailembur))) AS total_lembur
FROM tbl_karyawan, tbl_absen_lembur
WHERE tbl_karyawan.id_karyawan=tbl_absen_lembur.id_karyawan
AND month(tbl_absen_lembur.tgl)='01' AND year(tbl_absen_lembur.tgl)='2014'
GROUP BY tbl_absen_lembur.id_karyawan) AS lembur
ON tbl_karyawan.id_karyawan=lembur.id_karyawan

GROUP BY tbl_karyawan.id_karyawan ASC

Perhatikan saya menggunakan DATEDIFF untuk mencari rentang tanggal dan TIMEDIFF untuk mencari rentang waktu. Perhatikan pula huruf yang dicetak warna biru merupakan kunci dari setiap operasi join dengan rootnya.Yang dicetak warna hijau adalah nama kolom yang akan dimunculkan dan kaitannya dengan query join didalamnya. Sedangkan untuk huruf yang berwarna merah dapat anda ganti dengan pemanggilan dari PHP untuk report periodenya dengan metode $_GET[‘var’].

Apabila SQL diatas dieksekusi, maka data yang bisa saya dapatkan seperti dibawah ini

19-04-2014 21-31-33

Sebelum saya akhiri tulisan ini, untuk penggunaan hour(TIMEDIFF()) hanya akan menampilkan angka jam dengan pembulatan. Namun apabila kita ingin memodifikasi misalnya lama lembur adalah 2 jam 30 menit dengan format 2:30, maka perlu sedikit kita modifikasi. Logikanya jam kita ubah ke detik dicari totalnya dan dikembalikan ke jam. OK langsung saja untuk lebih jelasnya

SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( waktu_selesai, waktu_mulailembur)))), "%h:%i")
FROM tbl_absen_lembur
WHERE month(waktu_mulailembur)='01'

’01’ adalah untuk bulan januari. Sesuaikan dengan kebutuhan, bisa dengan metode $_GET[‘var’] atau $_POST[‘var’]

OK saya rasa sekian dulu tulisan saya ini. Mudah-mudahan bermanfaat.

Iklan