presensi

CREATE TABLE IF NOT EXISTS tb_karyawan(
  nik VARCHAR(10) NOT NULL PRIMARY KEY,
  nama VARCHAR(30) NOT NULL
) ENGINE=MyISAM;

INSERT INTO tb_karyawan
VALUES
  ('123456789','Dika Nanda'),
  ('1234567890','Andika Dwi Chahya'),
  ('2147483647','Akbar Alifian');

SELECT * FROM tb_karyawan;

+------------+-------------------+
| nik        | nama              |
+------------+-------------------+
| 123456789  | Dika Nanda        |
| 1234567890 | Andika Dwi Chahya |
| 2147483647 | Akbar Alifian     |
+------------+-------------------+
3 rows in set (0.00 sec)

CREATE TABLE IF NOT EXISTS tb_presensi(
  nik VARCHAR(10) NOT NULL,
  tanggal DATE,
  jam_masuk TIME,
  jam_pulang TIME,
  terlambat TIME,
  ket VARCHAR(30) NOT NULL
) ENGINE=MyISAM;

INSERT INTO tb_presensi
VALUES
  ('123456789','2014-10-15','11:30:17','00:00:00','03:30:00','masuk'),
  ('1234567890','2014-10-11','12:25:01','12:26:27','04:25:00','masuk'),
  ('1234567890','2014-10-12','15:02:42','00:00:00','07:02:00','masuk'),
  ('1234567890','2014-10-14','09:48:47','10:42:53','01:48:00','masuk');

SELECT * FROM tb_presensi;

+------------+------------+-----------+------------+-----------+-------+
| nik        | tanggal    | jam_masuk | jam_pulang | terlambat | ket   |
+------------+------------+-----------+------------+-----------+-------+
| 123456789  | 2014-10-15 | 11:30:17  | 00:00:00   | 03:30:00  | masuk |
| 1234567890 | 2014-10-11 | 12:25:01  | 12:26:27   | 04:25:00  | masuk |
| 1234567890 | 2014-10-12 | 15:02:42  | 00:00:00   | 07:02:00  | masuk |
| 1234567890 | 2014-10-14 | 09:48:47  | 10:42:53   | 01:48:00  | masuk |
+------------+------------+-----------+------------+-----------+-------+
4 rows in set (0.00 sec)

SELECT
  x1.tanggal,
  x1.nik,
  x1.nama,
  IF(x2.jam_masuk,x2.jam_masuk,'-') AS masuk,
  IF(x2.jam_pulang,x2.jam_pulang,'-') AS pulang,
  IF(x2.terlambat,x2.terlambat,'-') AS terlambat,
  IF(x2.ket IS NULL ,'tidak masuk',x2.ket) AS ket
FROM
  (
    SELECT
      z1.tanggal,z1.t1,z1.t2,
      z2.nik,z2.nama
    FROM
    (
      SELECT
        DATE_ADD(t.t1,INTERVAL td.digit DAY) AS tanggal,t.t1,t.t2
      FROM
        (
          SELECT
            (b1.d+b2.d+b4.d+b8.d+b16.d) AS digit
          FROM
            (SELECT 0 d UNION ALL SELECT 1 d ) b1
            CROSS JOIN (SELECT 0 d UNION ALL SELECT 2 d ) b2
            CROSS JOIN (SELECT 0 d UNION ALL SELECT 4 d ) b4
            CROSS JOIN (SELECT 0 d UNION ALL SELECT 8 d ) b8
            CROSS JOIN (SELECT 0 d UNION ALL SELECT 16 d ) b16
        ) td,
        (
          SELECT
            MIN(tanggal) AS t1, MAX(tanggal) AS t2
          FROM
            tb_presensi
        ) t
      ) z1
    ,
    (
      SELECT
        a.nik,a.nama
      FROM
        tb_karyawan a
    ) z2
  ) x1
  LEFT JOIN
    tb_presensi x2 USING(nik,tanggal)
WHERE
  x1.tanggal BETWEEN x1.t1 AND x1.t2
ORDER BY
  x1.tanggal,x1.nik;

+------------+------------+-------------------+----------+----------+-----------+-------------+
| tanggal    | nik        | nama              | masuk    | pulang   | terlambat| ket         |
+------------+------------+-------------------+----------+----------+-----------+-------------+
| 2014-10-11 | 123456789  | Dika Nanda        | -        | -        | -| tidak masuk |
| 2014-10-11 | 1234567890 | Andika Dwi Chahya | 12:25:01 | 12:26:27 | 04:25:00| masuk       |
| 2014-10-11 | 2147483647 | Akbar Alifian     | -        | -        | -| tidak masuk |
| 2014-10-12 | 123456789  | Dika Nanda        | -        | -        | -| tidak masuk |
| 2014-10-12 | 1234567890 | Andika Dwi Chahya | 15:02:42 | -        | 07:02:00| masuk       |
| 2014-10-12 | 2147483647 | Akbar Alifian     | -        | -        | -| tidak masuk |
| 2014-10-13 | 123456789  | Dika Nanda        | -        | -        | -| tidak masuk |
| 2014-10-13 | 1234567890 | Andika Dwi Chahya | -        | -        | -| tidak masuk |
| 2014-10-13 | 2147483647 | Akbar Alifian     | -        | -        | -| tidak masuk |
| 2014-10-14 | 123456789  | Dika Nanda        | -        | -        | -| tidak masuk |
| 2014-10-14 | 1234567890 | Andika Dwi Chahya | 09:48:47 | 10:42:53 | 01:48:00| masuk       |
| 2014-10-14 | 2147483647 | Akbar Alifian     | -        | -        | -| tidak masuk |
| 2014-10-15 | 123456789  | Dika Nanda        | 11:30:17 | -        | 03:30:00| masuk       |
| 2014-10-15 | 1234567890 | Andika Dwi Chahya | -        | -        | -| tidak masuk |
| 2014-10-15 | 2147483647 | Akbar Alifian     | -        | -        | -| tidak masuk |
+------------+------------+-------------------+----------+----------+-----------+-------------+
15 rows in set (0.00 sec)