Studi Kasus 2 FIREBIRD ( Menggunakan SQL & STORED PROCEDURE )

Kembali ke Materi sebelumnya di Studi Kasus 1 FIREBIRD, Apakah sudah mengerti mengenai Penyelesaian Kasusnya ?

Apabila sudah mengerti, mari kita lanjutkan ke Studi Kasus ke 2. Dan apabila belum, silahkan pelajari kembali di materi sebelumnya atau carilah materi lain tentang dasar-dasar SQL.

Karena di studi kasus ke-2, Sugi Smart Solution akan memberikan kasus yang lebih rumit dari Studi Kasus ke-1.

Mari kita mulai.

Kasus 3
Buatlah Query untuk menampilkan Rekap Absensi Karyawan Tanggal 05/21/2018 s/d 05/25/2018 ! ( Urutkan berdasarkan Nama )

Ada beberapa kriteria yang harus diperhatikan :

1. Apabila karyawan tidak memiliki JAM IN, JAM OUT dan tidak memiliki Keterangan di TMK. Maka Karyawan tersebut dikatakan ALPHA/Tidak Masuk Kerja.

Jawaban Kasus 3

Sebelum melihat jawaban di bawah ini,  silahkan dicoba terlebih dahulu untuk mendapatkan jawaban dari Kasus 3. Apabila sudah mencoba namun gagal, mari kita lihat Jawaban di bawah ini.

SELECT karyawan.nik,
karyawan.nama, 
coalesce(a1.keterangan,'') as tgl_21,
coalesce(a2.keterangan,'') as tgl_22,
coalesce(a3.keterangan,'') as tgl_23,
coalesce(a4.keterangan,'') as tgl_24,
coalesce(a5.keterangan,'') as tgl_25,
count(absensi.jam_in)-sum(iif(absensi.jam_in is not null and tmk.keterangan<>'',1,0)) as JmlHariKerja 

FROM karyawan 
LEFT JOIN absensi ON karyawan.nik=absensi.nik
LEFT JOIN TMK ON tmk.nik=absensi.nik AND absensi.tanggal=tmk.tanggal AND tmk.acc='Y' AND tmk.keterangan<>'Tugas Luar'

LEFT JOIN (SELECT ABSENSI.NIK,IIF(ABSENSI.JAM_IN IS NULL AND TMK.KETERANGAN IS NULL,'Alpha',TMK.KETERANGAN) as keterangan FROM ABSENSI LEFT JOIN TMK ON TMK.TANGGAL=ABSENSI.TANGGAL AND TMK.NIK=ABSENSI.NIK AND TMK.ACC='Y' WHERE ABSENSI.TANGGAL='05/21/2018') a1 ON a1.NIK=absensi.NIK

LEFT JOIN (SELECT ABSENSI.NIK,IIF(ABSENSI.JAM_IN IS NULL AND TMK.KETERANGAN IS NULL,'Alpha',TMK.KETERANGAN) as keterangan FROM ABSENSI LEFT JOIN TMK ON TMK.TANGGAL=ABSENSI.TANGGAL AND TMK.NIK=ABSENSI.NIK AND TMK.ACC='Y' WHERE ABSENSI.TANGGAL='05/22/2018') a2 ON a2.NIK=absensi.NIK

LEFT JOIN (SELECT ABSENSI.NIK,IIF(ABSENSI.JAM_IN IS NULL AND TMK.KETERANGAN IS NULL,'Alpha',TMK.KETERANGAN) as keterangan FROM ABSENSI LEFT JOIN TMK ON TMK.TANGGAL=ABSENSI.TANGGAL AND TMK.NIK=ABSENSI.NIK AND TMK.ACC='Y' WHERE ABSENSI.TANGGAL='05/23/2018') a3 ON a3.NIK=absensi.NIK

LEFT JOIN (SELECT ABSENSI.NIK,IIF(ABSENSI.JAM_IN IS NULL AND TMK.KETERANGAN IS NULL,'Alpha',TMK.KETERANGAN) as keterangan FROM ABSENSI LEFT JOIN TMK ON TMK.TANGGAL=ABSENSI.TANGGAL AND TMK.NIK=ABSENSI.NIK AND TMK.ACC='Y' WHERE ABSENSI.TANGGAL='05/24/2018') a4 ON a4.NIK=absensi.NIK

LEFT JOIN (SELECT ABSENSI.NIK,IIF(ABSENSI.JAM_IN IS NULL AND TMK.KETERANGAN IS NULL,'Alpha',TMK.KETERANGAN) as keterangan FROM ABSENSI LEFT JOIN TMK ON TMK.TANGGAL=ABSENSI.TANGGAL AND TMK.NIK=ABSENSI.NIK AND TMK.ACC='Y' WHERE ABSENSI.TANGGAL='05/25/2018') a5 ON a5.NIK=absensi.NIK

GROUP BY karyawan.nik,karyawan.nama,a1.keterangan,a2.keterangan,a3.keterangan,a4.keterangan,a5.keterangan
ORDER BY karyawan.nama ASC

Kasus 4
Buatlah Query untuk menampilkan Jumlah Jam Kerja Karyawan dari  Tanggal 05/21/2018 s/d 05/25/2018 ! ( Urutkan berdasarkan Jumlah Jam Kerja Tertinggi )

Ada beberapa kriteria yang harus diperhatikan :

1. Jam Kerja Senin-Jumat, mulai dari jam 08.30 – 17.30
2. Istirahat Senin-Kamis adalah jam 12.00-13.00
3. Istirahat Jumat adalah 11.30-13.00
4. Apabila Jam IN Karyawan kurang dari Jam IN Kerja (08.30), maka Jam IN Karyawan = Jam IN Kerja(08.30)
5. Apabila Jam OUT Karyawan melebihi dari Jam OUT Kerja (17.30), maka dibulatkan Jam OUT Karyawan 17.30 ( Tidak dianggap Lembur )

Jawaban Kasus 4

Melihat kasus 4 yang begitu kompleks, mari kita sederhanakan QUERY menggunakan STORED PROCEDURE.

Apa itu STORED PROCEDURE ?

STORED PROCEDURE adalah salah satu objek routine yang tersimpan pada basis data dan dapat digunakan untuk menggantikan berbagai kumpulan perintah yang sering kita gunakan. STORED PROCEDURE sangat berguna ketika kita tidak ingin pengguna mengakses tabel secara langsung, atau dengan kata lain membatasi hak akses pengguna dan mencatat operasi yang dilakukan.

Kembali ke IBEXPERT, Klik kanan pada PROCEDURES, lalu pilih New PROCEDURE, berikan nama PROCEDURE “SQV_GET_JAM_KERJA”

Ikuti langkah-langkah berikut ini :

Input Parameters 
PERIODE1 ( DATE), PERIODE2 (DATE)

Output Parameters
NIK (VARCHAR(10)), NAMA((VARCHAR(50)), JAM_KERJA(DOUBLE PRECISION)

Variables
TANGGAL(DATE), JAMKERJA1(DOUBLE PRECISION), JAM_IN(TIME), JAM_OUT(TIME), HARI(INTEGER)

Copykan SQL di bawah ini :

begin
  -- Mengambil Data Karyawan
  FOR SELECT NIK,NAMA FROM KARYAWAN INTO :NIK,:NAMA
  DO BEGIN
    JAMKERJA1 = 0;

    FOR SELECT DISTINCT TANGGAL FROM ABSENSI WHERE TANGGAL BETWEEN :PERIODE1 AND :PERIODE2 INTO :TANGGAL DO BEGIN
        -- Mengambil Data Jam IN dan Jam OUT
        SELECT JAM_IN, JAM_OUT FROM ABSENSI WHERE TANGGAL=:TANGGAL AND NIK=:NIK INTO :JAM_IN,:JAM_OUT;

        /*Apabila Jam IN Karyawan kurang dari Jam IN Kerja (08.30), maka Jam IN Karyawan = Jam IN Kerja(08.30)*/
        IF(JAM_IN < CAST('08:30:00' AS TIME)) THEN BEGIN
            JAM_IN = CAST('08:30:00' AS TIME);
        END

        /*Apabila Jam OUT Karyawan melebihi dari Jam OUT Kerja (17.30), maka dibulatkan Jam OUT Karyawan 17.30 ( Tidak dianggap Lembur )*/
        IF(JAM_OUT > CAST('17:30:00' AS TIME)) THEN BEGIN
            JAM_OUT = CAST('17:30:00' AS TIME);
        END

        SELECT EXTRACT(weekday FROM :TANGGAL) FROM RDB$DATABASE INTO :HARI;

        IF(HARI=5) THEN BEGIN
            /*Istirahat Jumat adalah 11.30-13.00*/
            JAMKERJA1 = JAMKERJA1 + COALESCE((JAM_OUT - JAM_IN - 5400),0);
        END
        ELSE BEGIN
            /*Istirahat Senin-Kamis adalah jam 12.00-13.00*/
            JAMKERJA1 = JAMKERJA1 + COALESCE((JAM_OUT - JAM_IN - 3600),0);
        END
    END
        JAM_KERJA=JAMKERJA1/3600;     -- Konversi ke Jam
        suspend;
  END
End

Demikianlah penjelasan Studi Kasus 2 FIREBIRD.
Semoga penjelasan yang diberikan memberikan manfaat bagi kita semua.

Terima kasih telah mengunjungi Website Sugi Smart Solution.