Lewati ke konten utama

Dokumentasi Program Laporan Bulanan — Kumpulan Query SQL

Produk/Unit: Operasional SJS / NES
Versi Dokumen: v1.0 (11 Nov 2025)
DB Target: MySQL 8.0+ / MariaDB 10.2+ (mendukung WITH/CTE)
Penulis: Program SJS

Daftar Isi​

  1. Pendahuluan & Konvensi
  2. Asumsi Skema dan Indeks Rekomendasi
  3. Query: Laporan Patroli per Shift
  4. Query: Buku Kendaraan per Shift
  5. Query: Buku Tamu per Shift
  6. Query: Buku Jurnal per Shift
  7. Query: Buku Penemuan Barang (Lost & Found) per Shift
  8. Query: Buku Serah Terima per Shift
  9. Query: Peserta Tes & Hasilnya (Detail per id_test)
  10. Query: Matriks Hasil Tes (Bucket 0–25 → 25%)
  11. Cara Eksekusi & Ekspor
  12. Validasi & Troubleshooting

Pendahuluan & Konvensi​

  • Seluruh query dirancang untuk laporan bulanan per site/customer dan/atau shift.
  • Gunakan variabel parameter untuk periode dan site/customer agar mudah dipindah‑pindah:
    • @tahun, @bulan → periode laporan
    • @customer_id, @area_id (opsional) → filter entitas
  • Beberapa query memerlukan join ke tabel shift untuk menampilkan semua shift walaupun tidak ada data (menggunakan LEFT JOIN + CTE master shift).
  • CTE (WITH) digunakan untuk keterbacaan dan komposisi logika; pastikan versi DB sudah mendukung.

Catatan Persentase: Pada bagian matriks tes, nilai 0% masuk bucket 25% sesuai kebijakan (0–25% ⇒ 25%).

Asumsi Skema dan Indeks Rekomendasi​

Tabel Inti yang Direferensikan​

  • shift (shift_id, shift_name, time_in, time_out, ...)
  • employees (id, employees_name, customer_id, shift_id, ...)
  • checkpoints (id, customer_id, shift_id, ...)
  • patrol_logs (checkpoint_id, checkpoint_date, session, ...)
  • urgent (checkpoint_id, urgent_date, urgent_date_end, status, ...)
  • vehicle_book (id_vehicle_book, id, id_area, vehicle_book_date, shift_id, ...)
  • add_vehicle (id_add_vehicle, id_vehicle_book, condition_vehicle, ...)
  • guest_book (id_guest, id, guest_date, clock_in, clock_out, ...) → id mengacu karyawan.
  • journal_book (id_journal, id, journal_date, o_clock, journal_book_photo, ...)
  • item_discovery (item_discovery_date, id, status, ...)
  • handover (handover_date, id, status, ...)
  • start_test (id_test, id_employees, test_date, ...)
  • results_test (id_result, id_test, id_question, answer, ...)
  • questions (question_id, answer, ...)
  • customers (customer_id, name, ...)

Indeks Rekomendasi (performa)​

  • patrol_logs (checkpoint_id, checkpoint_date, session)
  • urgent (checkpoint_id, urgent_date, status, urgent_date_end)
  • checkpoints (customer_id, shift_id)
  • vehicle_book (shift_id, vehicle_book_date, id, id_area)
  • add_vehicle (id_vehicle_book)
  • guest_book (id, guest_date, clock_in) + index di employees (id, customer_id)
  • journal_book (id, journal_date)
  • item_discovery (id, item_discovery_date, status)
  • handover (id, handover_date, status)
  • start_test (id_employees, test_date)
  • results_test (id_test, id_question)
  • questions (question_id)

Penamaan indeks dapat disesuaikan standar internal. Pastikan kolom tanggal/filter menjadi bagian dari indeks untuk mempercepat pemindaian bulanan.

Query: Laporan Patroli per Shift​

Tujuan: Rekap target/dijalankan dan status urgent per shift, termasuk baris Total dan % Total.
Input: @tahun, @bulan, @customer_id
Keluaran:

  • Shift, Target Patroli, Target Dijalankan, Jumlah Urgent, Urgent Belum Selesai
-- PARAMETER
SET @tahun := 2025;
SET @bulan := 11;
SET @customer_id := 109;

-- checkpoint per customer (bisa jadi banyak yg shift_id IS NULL)
WITH cp AS (
  SELECT c.id AS checkpoint_id, c.shift_id
  FROM checkpoints c
  WHERE c.customer_id = @customer_id
),
-- checkpoint yg dijalankan minimal 1x (end) di bulan tsb
pl AS (
  SELECT DISTINCT p.checkpoint_id
  FROM patrol_logs p
  JOIN cp ON cp.checkpoint_id = p.checkpoint_id
  WHERE YEAR(p.checkpoint_date)=@tahun
    AND MONTH(p.checkpoint_date)=@bulan
    AND p.session = 'end'
),
-- urgent per checkpoint di bulan tsb
ur AS (
  SELECT
    u.checkpoint_id,
    COUNT(*) AS urgent_total,
    -- yang BELUM selesai: N (open) + 1 (proses) + (opsional) status NULL tapi belum ada end
    SUM(CASE
          WHEN LOWER(u.status) IN ('n') THEN 1
          WHEN u.status = '1' THEN 1
          WHEN u.status IS NULL AND u.urgent_date_end IS NULL THEN 1
          ELSE 0
        END) AS urgent_open,
    -- kalau mau sekalian yang selesai:
    SUM(CASE
          WHEN u.status = '2' OR u.urgent_date_end IS NOT NULL THEN 1
          ELSE 0
        END) AS urgent_done
  FROM urgent u
  JOIN cp ON cp.checkpoint_id = u.checkpoint_id
  WHERE YEAR(u.urgent_date)=@tahun
    AND MONTH(u.urgent_date)=@bulan
  GROUP BY u.checkpoint_id
),

-- agregasi untuk shift YANG ADA di tabel shift (dinamis)
agg_shift AS (
  SELECT
    s.shift_id,
    s.shift_name,
    COUNT(DISTINCT cp.checkpoint_id)                                                AS target_patroli,
    COUNT(DISTINCT CASE WHEN pl.checkpoint_id IS NOT NULL THEN cp.checkpoint_id END) AS target_dijalankan,
    COALESCE(SUM(ur.urgent_total),0)                                               AS jumlah_urgent,
    COALESCE(SUM(ur.urgent_open),0)                                                AS urgent_belum_selesai
  FROM shift s
  LEFT JOIN cp ON cp.shift_id = s.shift_id
  LEFT JOIN pl ON pl.checkpoint_id = cp.checkpoint_id
  LEFT JOIN ur ON ur.checkpoint_id = cp.checkpoint_id
  GROUP BY s.shift_id, s.shift_name
),
-- agregasi untuk checkpoint TANPA shift (shift_id IS NULL)
agg_no_shift AS (
  SELECT
    NULL AS shift_id,
    'Tanpa Shift' AS shift_name,
    COUNT(DISTINCT cp.checkpoint_id)                                                AS target_patroli,
    COUNT(DISTINCT CASE WHEN pl.checkpoint_id IS NOT NULL THEN cp.checkpoint_id END) AS target_dijalankan,
    COALESCE(SUM(ur.urgent_total),0)                                               AS jumlah_urgent,
    COALESCE(SUM(ur.urgent_open),0)                                                AS urgent_belum_selesai
  FROM cp
  LEFT JOIN pl ON pl.checkpoint_id = cp.checkpoint_id
  LEFT JOIN ur ON ur.checkpoint_id = cp.checkpoint_id
  WHERE cp.shift_id IS NULL
),
agg AS (
  SELECT * FROM agg_shift
  UNION ALL
  SELECT * FROM agg_no_shift
),
sumx AS (
  SELECT
    SUM(target_patroli)        AS t_target,
    SUM(target_dijalankan)     AS t_jalan,
    SUM(jumlah_urgent)         AS t_urgent,
    SUM(urgent_belum_selesai)  AS t_open
  FROM agg
)
-- OUTPUT
SELECT label AS `Shift`,
       `Target Patroli`,
       `Target Dijalankan`,
       `Jumlah Urgent`,
       `Urgent Belum Selesai`
FROM (
  SELECT
    a.shift_name AS label,
    a.target_patroli        AS `Target Patroli`,
    a.target_dijalankan     AS `Target Dijalankan`,
    a.jumlah_urgent         AS `Jumlah Urgent`,
    a.urgent_belum_selesai  AS `Urgent Belum Selesai`,
    COALESCE(a.shift_id, 9998) AS urut
  FROM agg a

  UNION ALL
  SELECT 'Total', t_target, t_jalan, t_urgent, t_open, 9999 FROM sumx

  UNION ALL
  SELECT '% Total',
         100,
         ROUND(CASE WHEN t_target>0 THEN (t_jalan/t_target)*100 ELSE 0 END, 2),
         ROUND(CASE WHEN t_target>0 THEN (t_urgent/t_target)*100 ELSE 0 END, 2),
         ROUND(CASE WHEN t_target>0 THEN (t_open/t_target)*100 ELSE 0 END, 2),
         10000
  FROM sumx
) x
ORDER BY urut;

Query: Buku Kendaraan per Shift​

Tujuan: Rekap pemeriksaan kendaraan per shift, termasuk Total dan % Total (OK vs Tidak OK).
Input: @tahun, @bulan, @customer_id (opsional), @area_id (opsional)

-- =========================
-- PARAMETER
-- =========================
SET @tahun := 2025;
SET @bulan := 10;           -- contoh: Oktober
SET @customer_id := NULL;   -- isi vb.id (customer) bila mau filter khusus, NULL = semua
SET @area_id     := NULL;   -- opsional filter area

-- ==========================================
-- Basis data pemeriksaan kendaraan per shift
-- ==========================================
WITH base AS (
  SELECT
      s.shift_id,
      s.shift_name,
      av.id_add_vehicle,
      av.condition_vehicle
  FROM shift s
  LEFT JOIN vehicle_book vb
         ON vb.shift_id = s.shift_id
        AND YEAR(vb.vehicle_book_date) = @tahun
        AND MONTH(vb.vehicle_book_date) = @bulan
        AND (@customer_id IS NULL OR vb.id = @customer_id)
        AND (@area_id     IS NULL OR vb.id_area = @area_id)
  LEFT JOIN add_vehicle av
         ON av.id_vehicle_book = vb.id_vehicle_book
),
agg AS (
  SELECT
      shift_name,
      COUNT(av.id_add_vehicle)                                                   AS total_pemeriksaan,
      SUM(CASE WHEN LOWER(TRIM(av.condition_vehicle)) IN ('baik','ok','good')
               THEN 1 ELSE 0 END)                                               AS total_ok
  FROM base av
  GROUP BY shift_name
)
-- =========================
-- OUTPUT AKHIR (tabel)
-- =========================
SELECT
  label AS `Shift`,
  `Total Pemeriksaan`,
  `Total Kendaraan OK`,
  `Total Kendaraan Tidak OK`
FROM (
  -- per shift (mengikuti nama di tabel shift)
  SELECT
    a.shift_name AS label,
    a.total_pemeriksaan                           AS `Total Pemeriksaan`,
    a.total_ok                                    AS `Total Kendaraan OK`,
    (a.total_pemeriksaan - a.total_ok)            AS `Total Kendaraan Tidak OK`,
    CASE a.shift_name
      WHEN 'Pagi'  THEN 1
      WHEN 'Sore'  THEN 2
      WHEN 'Malam' THEN 3
      ELSE 9
    END AS urut
  FROM agg a

  UNION ALL
  -- Total
  SELECT
    'Total',
    SUM(total_pemeriksaan),
    SUM(total_ok),
    SUM(total_pemeriksaan) - SUM(total_ok),
    10
  FROM agg

  UNION ALL
  -- % Total (kolom 2–4 dalam persentase dari total pemeriksaan)
  SELECT
    '% Total',
    100,
    ROUND( (SUM(total_ok) / NULLIF(SUM(total_pemeriksaan),0)) * 100, 2 ),
    ROUND( ((SUM(total_pemeriksaan) - SUM(total_ok)) / NULLIF(SUM(total_pemeriksaan),0)) * 100, 2 ),
    11
  FROM agg
) x
ORDER BY urut;

Query: Buku Tamu per Shift​

Tujuan: Rekap kunjungan tamu per shift dengan mapping jam ke slot shift (termasuk shift yang melewati tengah malam).
Input: @tahun, @bulan, @customer_id

-- PARAMETER
SET @tahun := 2025;
SET @bulan := 10;           -- November
SET @customer_id := 10;    -- filter site/customer via employees

WITH base AS (
  SELECT
    g.id_guest,
    g.guest_date,
    g.clock_in,
    g.clock_out,
    s.shift_name
  FROM guest_book g
  JOIN employees e
       ON e.id = g.id              -- << relasi tamu -> karyawan
  JOIN shift s
    ON (
      (s.time_out > s.time_in
           AND g.clock_in >= s.time_in AND g.clock_in <  s.time_out) OR
      (s.time_out <= s.time_in           -- shift melintasi 00:00
           AND (g.clock_in >= s.time_in OR g.clock_in < s.time_out))
    )
  WHERE YEAR(g.guest_date) = @tahun
    AND MONTH(g.guest_date) = @bulan
    AND e.customer_id = @customer_id     -- << filter di sini
),
agg AS (
  SELECT
    shift_name,
    COUNT(*)                                          AS total_kunjungan,
    SUM(CASE WHEN clock_out IS NOT NULL THEN 1 ELSE 0 END) AS total_sudah_checkout,
    SUM(CASE WHEN clock_out IS NULL     THEN 1 ELSE 0 END) AS total_belum_checkout
  FROM base
  GROUP BY shift_name
),
sumx AS (
  SELECT
    SUM(total_kunjungan)      AS s_total,
    SUM(total_sudah_checkout) AS s_sudah,
    SUM(total_belum_checkout) AS s_belum
  FROM agg
)
SELECT tampil AS `Shift`,
       `Total Kunjungan`,
       `Total Sudah Checkout`,
       `Total Belum Checkout`
FROM (
  -- per-shift
  SELECT
    CASE a.shift_name
      WHEN 'Pagi'  THEN 'Pagi'
      WHEN 'Sore'  THEN 'Sore'
      WHEN 'Malam' THEN 'Malam'
      ELSE COALESCE(a.shift_name, 'Lainnya')
    END AS tampil,
    a.total_kunjungan        AS `Total Kunjungan`,
    a.total_sudah_checkout   AS `Total Sudah Checkout`,
    a.total_belum_checkout   AS `Total Belum Checkout`,
    CASE a.shift_name WHEN 'Pagi' THEN 1 WHEN 'Sore' THEN 2 WHEN 'Malam' THEN 3 ELSE 9 END AS urut
  FROM agg a

  UNION ALL
  -- TOTAL
  SELECT 'Total', s_total, s_sudah, s_belum, 10 FROM sumx

  UNION ALL
  -- % TOTAL
  SELECT '% Total',
         100,
         ROUND(CASE WHEN s_total>0 THEN (s_sudah/s_total)*100 ELSE 0 END, 2),
         ROUND(CASE WHEN s_total>0 THEN (s_belum/s_total)*100 ELSE 0 END, 2),
         11
  FROM sumx
) z
ORDER BY urut;

Query: Buku Jurnal per Shift​

Tujuan: Rekap total entri, hari aktif (distinct), rata‑rata entri/hari, entri berfoto, % entri berfoto, dan share % dari total.
Input: @tahun, @bulan, @customer_id
Catatan: Menggunakan CROSS JOIN ke agregat total untuk menghitung %Global.

-- =========================
-- PARAMETER
-- =========================
SET @tahun        := 2025;
SET @bulan        := 10;     -- 1..12
SET @customer_id  := 10;     -- filter customer

-- Periode [start_date, end_date)
SET @start_date := STR_TO_DATE(CONCAT(@tahun,'-',LPAD(@bulan,2,'0'),'-01'),'%Y-%m-%d');
SET @end_date   := DATE_ADD(@start_date, INTERVAL 1 MONTH);

-- =========================
-- REKAP DATA JURNAL: semua shift + Total + % Total
-- =========================
WITH sh AS (  -- daftar SEMUA shift (dinamis, tanpa hardcode)
  SELECT s.shift_id, s.shift_name
  FROM shift s
),
jb_filtered AS (  -- jurnal sesuai periode & customer (ambil shift dari employees)
  SELECT
    e.shift_id,
    j.id_journal,
    j.journal_date,
    j.o_clock,
    j.journal_book_photo
  FROM journal_book j
  JOIN employees e ON e.id = j.id         -- j.id -> employees.id
  WHERE j.journal_date >= @start_date
    AND j.journal_date <  @end_date
    AND e.customer_id    =  @customer_id
),
agg AS (  -- agregasi per shift; LEFT JOIN agar shift tanpa entri tetap muncul (0)
  SELECT
    sh.shift_id,
    sh.shift_name,
    COUNT(j.id_journal) AS total_entri,
    COUNT(DISTINCT j.journal_date) AS hari_aktif,
    SUM(CASE WHEN j.journal_book_photo IS NOT NULL AND TRIM(j.journal_book_photo) <> '' THEN 1 ELSE 0 END) AS entri_berfoto
  FROM sh
  LEFT JOIN jb_filtered j ON j.shift_id = sh.shift_id
  GROUP BY sh.shift_id, sh.shift_name
),
sumx AS (  -- total semua shift (untuk baris Total / % Total)
  SELECT
    SUM(total_entri)   AS t_total,
    SUM(entri_berfoto) AS t_foto
  FROM agg
),
global_days AS ( -- total hari aktif global (distinct tanggal di seluruh shift)
  SELECT COUNT(DISTINCT journal_date) AS g_days
  FROM jb_filtered
)

SELECT label AS `Shift`,
       `Total Entri Jurnal`,
       `Total Hari Aktif`,
       `Rata-rata Entri/Hari`,
       `Entri Berfoto`,
       `% Entri Berfoto`,
       `% dari Total`
FROM (
  -- baris per shift
  SELECT
    a.shift_name AS label,
    a.total_entri                                             AS `Total Entri Jurnal`,
    a.hari_aktif                                              AS `Total Hari Aktif`,
    ROUND(a.total_entri / NULLIF(a.hari_aktif,0), 2)          AS `Rata-rata Entri/Hari`,
    a.entri_berfoto                                           AS `Entri Berfoto`,
    ROUND(a.entri_berfoto / NULLIF(a.total_entri,0) * 100, 2) AS `% Entri Berfoto`,
    -- share per shift terhadap total entri semua shift
    ROUND(a.total_entri / NULLIF(sx.t_total,0) * 100, 2)      AS `% dari Total`,
    a.shift_id                                                AS urut
  FROM agg a
  CROSS JOIN sumx sx

  UNION ALL
  -- baris TOTAL (jumlah keseluruhan)
  SELECT
    'Total' AS label,
    sx.t_total AS `Total Entri Jurnal`,
    NULL      AS `Total Hari Aktif`,              -- opsional: bisa SUM(a.hari_aktif) kalau mau jumlah per shift
    ROUND(sx.t_total / NULLIF(gd.g_days,0), 2) AS `Rata-rata Entri/Hari`,
    sx.t_foto  AS `Entri Berfoto`,
    ROUND(sx.t_foto / NULLIF(sx.t_total,0) * 100, 2) AS `% Entri Berfoto`,
    100.00    AS `% dari Total`,
    9999      AS urut
  FROM sumx sx
  CROSS JOIN global_days gd

  UNION ALL
  -- baris % TOTAL (format seperti contohmu: kolom pertama = 100, yang lain persentase global)
  SELECT
    '% Total' AS label,
    100.00    AS `Total Entri Jurnal`,
    NULL      AS `Total Hari Aktif`,
    ROUND(sx.t_total / NULLIF(gd.g_days,0), 2)   AS `Rata-rata Entri/Hari`,
    NULL      AS `Entri Berfoto`,
    ROUND(sx.t_foto / NULLIF(sx.t_total,0) * 100, 2) AS `% Entri Berfoto`,
    100.00    AS `% dari Total`,
    10000     AS urut
  FROM sumx sx
  CROSS JOIN global_days gd
) x
ORDER BY urut;

Query: Buku Penemuan Barang (Lost & Found) per Shift​

Tujuan: Rekap penemuan barang per shift, memisahkan status 1 = proses, 2 = selesai. Status lain seperti N tidak dihitung.
Input: @tahun, @bulan, @customer_id

-- =========================
-- PARAMETER
-- =========================
SET @tahun       := 2025;
SET @bulan       := 10;      -- 1..12
SET @customer_id := 109;     -- filter customer

-- Periode [start_date, end_date)
SET @start_date := STR_TO_DATE(CONCAT(@tahun,'-',LPAD(@bulan,2,'0'),'-01'),'%Y-%m-%d');
SET @end_date   := DATE_ADD(@start_date, INTERVAL 1 MONTH);

-- =========================
-- LOST & FOUND per SHIFT (tanpa N; semua shift + Total + % Total)
-- =========================
WITH sh AS (  -- master semua shift (agar shift tanpa data tetap tampil)
  SELECT s.shift_id, s.shift_name
  FROM shift s
),

lf_filtered AS (  -- data L&F periode & customer (shift dari employees)
  SELECT
    e.shift_id,
    UPPER(TRIM(id.status)) AS st
  FROM item_discovery id
  JOIN employees e ON e.id = id.id
  WHERE id.item_discovery_date >= @start_date
    AND id.item_discovery_date <  @end_date
    AND e.customer_id = @customer_id
),

agg AS (  -- agregasi per shift; total = 1 + 2 (tanpa N)
  SELECT
    sh.shift_id,
    sh.shift_name,
    COALESCE(SUM(CASE WHEN lf.st = '1' THEN 1 ELSE 0 END),0) AS total_proses,   -- 1
    COALESCE(SUM(CASE WHEN lf.st = '2' THEN 1 ELSE 0 END),0) AS total_selesai,  -- 2
    COALESCE(SUM(CASE WHEN lf.st IN ('1','2') THEN 1 ELSE 0 END),0) AS total_penemuan
  FROM sh
  LEFT JOIN lf_filtered lf ON lf.shift_id = sh.shift_id
  GROUP BY sh.shift_id, sh.shift_name
),

sumx AS (  -- total keseluruhan
  SELECT
    SUM(total_penemuan) AS t_all,
    SUM(total_proses)   AS t_1,
    SUM(total_selesai)  AS t_2
  FROM agg
)

-- =========================
-- OUTPUT: per shift, lalu Total, lalu % Total
-- =========================
SELECT label AS `Shift`,
       `Total Penemuan`,
       `Total Proses (1)`,
       `Total Selesai (2)`
FROM (
  -- per shift
  SELECT
    a.shift_name     AS label,
    a.total_penemuan AS `Total Penemuan`,
    a.total_proses   AS `Total Proses (1)`,
    a.total_selesai  AS `Total Selesai (2)`,
    a.shift_id       AS urut
  FROM agg a

  UNION ALL
  -- TOTAL
  SELECT
    'Total',
    sx.t_all,
    sx.t_1,
    sx.t_2,
    9999
  FROM sumx sx

  UNION ALL
  -- % TOTAL (kolom pertama 100; kolom lain % dari total penemuan)
  SELECT
    '% Total',
    100,
    ROUND(CASE WHEN sx.t_all>0 THEN (sx.t_1/sx.t_all)*100 ELSE 0 END, 2),
    ROUND(CASE WHEN sx.t_all>0 THEN (sx.t_2/sx.t_all)*100 ELSE 0 END, 2),
    10000
  FROM sumx sx
) x
ORDER BY urut;

Query: Buku Serah Terima per Shift​

Tujuan: Rekap handover per shift, mengabaikan status N, menampilkan Total dan % Total.
Input: @tahun, @bulan, @customer_id

-- =========================
-- PARAMETER
-- =========================
SET @tahun       := 2025;
SET @bulan       := 11;      -- 1..12
SET @customer_id := 109;     -- filter customer

-- Periode [start_date, end_date)
SET @start_date := STR_TO_DATE(CONCAT(@tahun,'-',LPAD(@bulan,2,'0'),'-01'),'%Y-%m-%d');
SET @end_date   := DATE_ADD(@start_date, INTERVAL 1 MONTH);

-- =========================
-- HANDOVER per SHIFT (tanpa status 'N'; semua shift + Total + % Total)
-- =========================
WITH sh AS (  -- master semua shift (agar shift tanpa data tetap tampil)
  SELECT s.shift_id, s.shift_name
  FROM shift s
),

ho_filtered AS (  -- data handover periode & customer (shift dari employees)
  SELECT
    e.shift_id,
    TRIM(h.status) AS st
  FROM handover h
  JOIN employees e ON e.id = h.id           -- h.id = employees.id
  WHERE h.handover_date >= @start_date
    AND h.handover_date <  @end_date
    AND e.customer_id = @customer_id
),

agg AS (  -- agregasi per shift; total = status 1 + 2
  SELECT
    sh.shift_id,
    sh.shift_name,
    COALESCE(SUM(CASE WHEN ho.st = '1' THEN 1 ELSE 0 END),0) AS total_proses,   -- 1
    COALESCE(SUM(CASE WHEN ho.st = '2' THEN 1 ELSE 0 END),0) AS total_selesai,  -- 2
    COALESCE(SUM(CASE WHEN ho.st IN ('1','2') THEN 1 ELSE 0 END),0) AS total_penyerahan
  FROM sh
  LEFT JOIN ho_filtered ho ON ho.shift_id = sh.shift_id
  GROUP BY sh.shift_id, sh.shift_name
),

sumx AS (  -- total keseluruhan
  SELECT
    SUM(total_penyerahan) AS t_all,
    SUM(total_proses)     AS t_1,
    SUM(total_selesai)    AS t_2
  FROM agg
)

-- =========================
-- OUTPUT: per shift, lalu Total, lalu % Total
-- =========================
SELECT label AS `Shift`,
       `Total Penyerahan`,
       `Total Proses (1)`,
       `Total Selesai (2)`
FROM (
  -- per shift
  SELECT
    a.shift_name       AS label,
    a.total_penyerahan AS `Total Penyerahan`,
    a.total_proses     AS `Total Proses (1)`,
    a.total_selesai    AS `Total Selesai (2)`,
    a.shift_id         AS urut
  FROM agg a

  UNION ALL
  -- TOTAL
  SELECT
    'Total',
    sx.t_all,
    sx.t_1,
    sx.t_2,
    9999
  FROM sumx sx

  UNION ALL
  -- % TOTAL (kolom pertama 100; kolom lain % dari total penyerahan)
  SELECT
    '% Total',
    100,
    ROUND(CASE WHEN sx.t_all>0 THEN (sx.t_1/sx.t_all)*100 ELSE 0 END, 2),
    ROUND(CASE WHEN sx.t_all>0 THEN (sx.t_2/sx.t_all)*100 ELSE 0 END, 2),
    10000
  FROM sumx sx
) x
ORDER BY urut;

Query: Peserta Tes & Hasilnya (Detail per id_test)​

Tujuan: Menampilkan daftar peserta tes di periode tertentu beserta total soal, total benar/salah, dan persentase benar.
Input: @tahun, @bulan, @customer_id

-- =========================
-- PARAMETER
-- =========================
SET @tahun       := 2025;
SET @bulan       := 10;        -- 1..12
SET @customer_id := 109;

-- Periode [start_date, end_date)
SET @start_date := STR_TO_DATE(CONCAT(@tahun,'-',LPAD(@bulan,2,'0'),'-01'),'%Y-%m-%d');
SET @end_date   := DATE_ADD(@start_date, INTERVAL 1 MONTH);

-- =========================
-- DETAIL PER TES (per id_test) + total benar/salah
-- =========================
WITH tests AS (  -- semua tes di bulan & customer terpilih
  SELECT
    st.id_test,
    st.test_date,
    st.id_employees
  FROM start_test st
  JOIN employees e ON e.id = st.id_employees
  WHERE st.test_date >= @start_date
    AND st.test_date <  @end_date
    AND e.customer_id = @customer_id
),
scores AS (      -- hitung total soal & benar untuk setiap id_test
  SELECT
    t.id_test,
    COUNT(rt.id_result) AS total_soal,
    SUM(
      CASE
        WHEN rt.answer IS NOT NULL AND q.answer IS NOT NULL
             AND TRIM(LOWER(rt.answer)) = TRIM(LOWER(q.answer))
        THEN 1 ELSE 0
      END
    ) AS total_benar
  FROM tests t
  LEFT JOIN results_test rt ON rt.id_test = t.id_test
  LEFT JOIN questions    q  ON q.question_id = rt.id_question
  GROUP BY t.id_test
)
SELECT
  c.customer_id                 AS site_id,
  c.name                        AS site_name,
  e.id                          AS employee_id,
  e.employees_name              AS employee_name,
  s.shift_name                  AS shift_name,
  st.id_test,
  st.test_date,
  sc.total_soal,
  sc.total_benar,
  (sc.total_soal - sc.total_benar)              AS total_salah,
  ROUND(sc.total_benar / NULLIF(sc.total_soal,0) * 100, 2) AS persen_benar
FROM tests st
JOIN employees e  ON e.id = st.id_employees
JOIN customers c  ON c.customer_id = e.customer_id
LEFT JOIN shift s ON s.shift_id = e.shift_id
LEFT JOIN scores sc ON sc.id_test = st.id_test
ORDER BY st.test_date, e.id, st.id_test;

Query: Matriks Hasil Tes (Bucket 0–25 → 25%)​

Tujuan: Membuat matriks distribusi karyawan berdasarkan nilai tes terbaru di bulan berjalan ke dalam bucket BELUM, 25, 50, 75, 100.
Aturan: 0–25% dimasukkan ke 25% (termasuk 0).
Input: @tahun, @bulan, @customer_id

-- =========================
-- PARAMETER
-- =========================
SET @tahun       := 2025;
SET @bulan       := 11;          -- 1..12
SET @customer_id := 109;         -- site/customer

-- Periode [start_date, end_date)
SET @start_date := STR_TO_DATE(CONCAT(@tahun,'-',LPAD(@bulan,2,'0'),'-01'),'%Y-%m-%d');
SET @end_date   := DATE_ADD(@start_date, INTERVAL 1 MONTH);

-- =========================
-- DATA MATRIKS (berbasis hasil tes; 0–25% => bucket 25%)
-- =========================
WITH emp AS (   -- seluruh karyawan di customer
  SELECT e.id
  FROM employees e
  WHERE e.customer_id = @customer_id
),
-- tes terbaru DALAM bulan tsb per karyawan
latest_test AS (
  SELECT st.id_test, st.id_employees
  FROM start_test st
  JOIN (
    SELECT id_employees, MAX(COALESCE(test_date, DATE('1900-01-01'))) AS mx
    FROM start_test
    WHERE test_date >= @start_date AND test_date < @end_date
    GROUP BY id_employees
  ) x ON x.id_employees = st.id_employees AND x.mx = COALESCE(st.test_date, DATE('1900-01-01'))
),
-- skor persen per id_test (jawaban benar / total * 100)
score AS (
  SELECT
    lt.id_employees,
    lt.id_test,
    ROUND(
      SUM(CASE WHEN rt.answer IS NOT NULL AND q.answer IS NOT NULL
               AND TRIM(LOWER(rt.answer)) = TRIM(LOWER(q.answer)) THEN 1 ELSE 0 END)
      / NULLIF(COUNT(q.question_id),0) * 100, 2
    ) AS pct
  FROM latest_test lt
  JOIN results_test rt ON rt.id_test = lt.id_test
  JOIN questions   q  ON q.question_id = rt.id_question
  GROUP BY lt.id_employees, lt.id_test
),
-- mapping: gabung semua karyawan + nilai (yang tak punya tes → NULL)
mapped AS (
  SELECT
    e.id AS employee_id,
    sc.pct
  FROM emp e
  LEFT JOIN score sc ON sc.id_employees = e.id
),
-- bucket range (0–25 = 25%; NULL = BELUM)
 bucketed AS (
  SELECT
    employee_id,
    CASE
      WHEN pct IS NULL                              THEN 'BELUM'
      WHEN pct BETWEEN  0 AND 25                    THEN '25'
      WHEN pct BETWEEN 26 AND 50                    THEN '50'
      WHEN pct BETWEEN 51 AND 75                    THEN '75'
      WHEN pct BETWEEN 76 AND 100                   THEN '100'
      ELSE 'BELUM'
    END AS bucket
  FROM mapped
),
agg AS (
  SELECT
    COUNT(*)            AS total_emp,
    SUM(bucket='BELUM') AS total_belum,
    SUM(bucket='25')    AS total_25,
    SUM(bucket='50')    AS total_50,
    SUM(bucket='75')    AS total_75,
    SUM(bucket='100')   AS total_100
  FROM bucketed
)

-- OUTPUT: baris Total dan % Total
SELECT baris AS `Shift`,
       `Jumlah Pegawai`,
       `Total Belum Ujian`,
       `Total 25%`,
       `Total 50%`,
       `Total 75%`,
       `Total 100%`
FROM (
  SELECT
    'Total' AS baris,
    total_emp   AS `Jumlah Pegawai`,
    total_belum AS `Total Belum Ujian`,
    total_25    AS `Total 25%`,
    total_50    AS `Total 50%`,
    total_75    AS `Total 75%`,
    total_100   AS `Total 100%`,
    1 AS urut
  FROM agg

  UNION ALL
  SELECT
    '% Total',
    100,
    ROUND(total_belum/NULLIF(total_emp,0)*100, 2),
    ROUND(total_25  /NULLIF(total_emp,0)*100, 2),
    ROUND(total_50  /NULLIF(total_emp,0)*100, 2),
    ROUND(total_75  /NULLIF(total_emp,0)*100, 2),
    ROUND(total_100 /NULLIF(total_emp,0)*100, 2),
    2
  FROM agg
) z
ORDER BY urut;

Cara Eksekusi & Ekspor​

  • Jalankan query di MySQL Workbench / DBeaver / CLI setelah mengganti parameter sesuai kebutuhan.
  • Contoh ekspor via CLI ke CSV:
mysql -u <user> -p -h <host> <db> -e "SOURCE laporan_patroli.sql" \
  | sed 's/\t/,/g' > patroli_2025_11.csv
  • Gunakan FORMAT=CSV (bila menggunakan tool lain) atau feature export pada GUI.

Validasi & Troubleshooting​

Checklist Validasi:

  • Periode (@tahun, @bulan) benar dan konsisten antar query.
  • @customer_id/@area_id sesuai kebutuhan filtering.
  • Tabel master shift sudah berisi semua shift aktif (agar baris tetap muncul saat tidak ada data).
  • Index‑index utama sudah ada (lihat bagian indeks rekomendasi) untuk menghindari query lambat.
  • Kolom status konsisten (mis: '1', '2', 'N', case sensitivity).

Masalah Umum & Solusi:

  1. CTE tidak dikenali → pastikan versi MySQL/MariaDB mendukung WITH. Alternatif: pecah jadi view/temp table.
  2. Hasil kosong → cek parameter periode dan relasi kunci asing (mis. handover.id → employees.id).
  3. Mapping shift jam salah → verifikasi time_in/time_out dan kasus melintasi 00:00.
  4. Persentase NaN/NULL → gunakan NULLIF(divisor,0) seperti di contoh.
  5. Performansi lambat → tambahkan indeks di kolom tanggal, foreign key, dan kolom filter.

Selesai. Jika diperlukan, dokumen ini dapat dijadikan template dan di‑versioning per periode/site. Pastikan untuk menyimpan salinan .sql tiap query di repositori internal (mis. reports/monthly/<tahun>/<bulan>/).