Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT `v_report_att`.`UserID` AS `USERID`,
- `v_report_att`.`nama_pegawai` AS `nama_pegawai`,
- DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m') AS `tahun_bulan`,
- DATE_FORMAT(`v_report_att`.`Date`,'%Y') AS `tahun`,
- DATE_FORMAT(`v_report_att`.`Date`,'%m') AS `bulann`,
- DATE_FORMAT(`v_report_att`.`Date`,'%M') AS `bulan`,
- TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(`v_report_att`.`LateInHour`))),'%H:%i:%s') AS `telat`,
- (SELECT COUNT(`t_attendance`.`StatusFin`)
- FROM `t_attendance`
- WHERE ((`t_attendance`.`StatusFin` = 'Sakit')
- AND (`t_attendance`.`UserID` = `v_report_att`.`UserID`)
- AND (DATE_FORMAT(`t_attendance`.`Date`,'%Y-%m') = DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')))) AS `sakit`,
- (SELECT COUNT(`t_attendance`.`StatusFin`)
- FROM `t_attendance`
- WHERE ((`t_attendance`.`StatusFin` = 'Cuti')
- AND (`t_attendance`.`UserID` = `v_report_att`.`UserID`)
- AND (DATE_FORMAT(`t_attendance`.`Date`,'%Y-%m') = DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')))) AS `cuti`,
- (SELECT COUNT(`t_attendance`.`StatusFin`)
- FROM `t_attendance`
- WHERE ((`t_attendance`.`StatusFin` = 'Ijin')
- AND (`t_attendance`.`UserID` = `v_report_att`.`UserID`)
- AND (DATE_FORMAT(`t_attendance`.`Date`,'%Y-%m') = DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')))) AS `ijin`,
- (SELECT COUNT(`t_attendance`.`StatusFin`)
- FROM `t_attendance`
- WHERE ((`t_attendance`.`StatusFin` = 'Terlambat')
- AND (`t_attendance`.`UserID` = `v_report_att`.`UserID`)
- AND (DATE_FORMAT(`t_attendance`.`Date`,'%Y-%m') = DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')))) AS `freq_telat`,
- (SELECT COUNT(`t_attendance`.`StatusFin`)
- FROM `t_attendance`
- WHERE ((`t_attendance`.`StatusFin` = 'Alfa')
- AND (`t_attendance`.`UserID` = `v_report_att`.`UserID`)
- AND (DATE_FORMAT(`t_attendance`.`Date`,'%Y-%m') = DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')))) AS `alfa`
- FROM `v_report_att`
- GROUP BY `v_report_att`.`UserID`,
- DATE_FORMAT(`v_report_att`.`Date`,'%Y-%m')
- ORDER BY `v_report_att`.`UserID`,
- `bulann`
Advertisement
Add Comment
Please, Sign In to add comment