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.

JOIN multiple table dengan operasi SUM beberapa kolom didalamnya

Tulisan ini mengawali tahun 2011 dimana saya punya permasalahan untuk menampilkan hasil query dari beberapa tabel dimana pada beberapa tabel membutuhkan operasi SUM (penjumlahan). Dari beberapa kali surfing, kita bisa melakukan operasi JOIN pada beberapa tabel. Namun demikian akan muncul permasalahan apabila dari beberapa tabel tersebut dibutuhkan operasi penjumlahan. Hasil yang kita peroleh terhadap penjumlahan tersebut adalah salah, saya tidak bisa menjelaskan secara panjang kesalahan tersebut, namun untuk mengetahui mengapa silahkan dicoba query secara terpisah.

Ok langsung saja dengan contoh saya memiliki 3 buah tabel

1. Tabel data_obat. Tabel ini memuat data kode dan nama obat dengan contoh seperti ini

2. Tabel supplier_obat. Tabel ini memuat pengiriman obat oleh supplier

3. Tabel pakai_obat. Tabel ini digunakan untuk menampung data pemakaian obat

Sekarang kita ingin menampilkan data rekap pemakain seluruh obat dengan kolom : kode, nama_obat, stok, pemakaian
Stok diambil dari jumlah obat berdasar kode dari supplier, sedangkan pemakaian diambil dari jumlah pemakaian obat berdasar jenis obat. Kita akan menggunakan LEFT JOIN untuk menampilkan semua data obat terhadap tabel lain.

Menampilkan data stok

SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok
FROM data_obat LEFT JOIN supplier_obat
ON data_obat.kode=supplier_obat.kode
GROUP BY data_obat.kode ASC

Menampilkan data pemakaian

SELECT data_obat.kode, data_obat.nama_obat, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat LEFT JOIN pakai_obat
ON data_obat.kode=pakai_obat.kode
GROUP BY data_obat.kode ASC

Kita akan menggunakan 2 operasi LEFT JOIN
SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat
LEFT JOIN supplier_obat ON data_obat.kode=supplier_obat.kode
LEFT JOIN pakai_obat ON pakai_obat.kode=data_obat.kode
GROUP BY data_obat.kode ASC

Ternyata data stok menunjukkan hasil yang salah bila kita bandingkan dengan query secara terpisah. Kenapa demikian? Hal ini disebabkan transaksi penggunaan obat lebih dari 1x maka stok akan dikalikan dengan berapa kali obat tersebut dipakai.

SOLUSI

SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok, pemakaian
FROM data_obat
LEFT JOIN supplier_obat ON data_obat.kode=supplier_obat.kode
LEFT JOIN
(SELECT data_obat.kode, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat, pakai_obat
WHERE data_obat.kode=pakai_obat.kode
GROUP BY pakai_obat.kode ASC) AS pakai
ON data_obat.kode=pakai.kode
GROUP BY data_obat.kode ASC

Semoga membantu 🙂