ais_vabsensi_rekap.sql

CREATE VIEW `ais_vabsensi_rekap` AS SELECT `ais_vabsensi`.`uid` AS `uid`,`ais_vabsensi`.`nama` AS `nama`,LEFT(`ais_vabsensi`.`tgl`,7) AS `bulan`,SUM(`ais_vabsensi`.`sakit`) AS `sakit`,SUM(`ais_vabsensi`.`ijin`) AS `ijin`,SUM(`ais_vabsensi`.`cuti`) AS `cuti`,SUM(`ais_vabsensi`.`mangkir`) AS `mangkir`,SUM(`ais_vabsensi`.`setengah_hari`) AS `setengah_hari`,SUM(`ais_vabsensi`.`terlambat1`) AS `terlambat1`,SUM(`ais_vabsensi`.`terlambat2`) AS `terlambat2`,sec_to_time(SUM(time_to_sec(`ais_vabsensi`.`lembur_masuk`))) AS `lembur_masuk`,sec_to_time(SUM(time_to_sec(`ais_vabsensi`.`lembur_pulang`))) AS `lembur_pulang`,SUM(`ais_vabsensi`.`um_lembur`) AS `um_lembur`,(((((COUNT(`ais_vabsensi`.`tgl`) - SUM(`ais_vabsensi`.`sakit`)) - SUM(`ais_vabsensi`.`ijin`)) - SUM(`ais_vabsensi`.`cuti`)) - SUM(`ais_vabsensi`.`mangkir`)) - SUM(`ais_vabsensi`.`extra_lembur`)) AS `hari_kerja` FROM `advess_ais2`.`ais_vabsensi` GROUP BY `ais_vabsensi`.`uid`,LEFT(`ais_vabsensi`.`tgl`,7) ORDER BY `ais_vabsensi`.`nama`;