SELECT `b`.`id`, `b`.`customerid`, `b`.`date`, `b`.`description`, `b`.`destination`, `b`.`parentCopyid`, `b`.`department_id` as `movementDept`, `b`.`created_by`, `b`.`status`, `a`.`id` as `detailid`, `a`.`staffid`, `a`.`timeOut`, `a`.`timeIn`, `c`.`id` as `typeid`, `c`.`color_code`, `c`.`movementType`, `d`.`name` as `staffName`, `e`.`title` as `staffPosition`, `f`.`id` as `departmentid`, `f`.`name` as `departmentName`, `g`.`name` as `customerName`, `h`.`name` as `creator`
FROM `hr__movement_group_detail` `a`
JOIN `hr__movement_group` `b` ON `a`.`groupid` = `b`.`id`
LEFT JOIN `customer` `g` ON `b`.`customerid` = `g`.`id`
LEFT JOIN `hr__movement_type` `c` ON `c`.`id` = `b`.`movementType`
LEFT JOIN `user` `d` ON `d`.`id` = `a`.`staffid`
LEFT JOIN `user` `h` ON `h`.`id` = `b`.`created_by`
LEFT JOIN `user__employment_detail` `e` ON `e`.`user_id` = `d`.`id`
LEFT JOIN `company__department` `f` ON `f`.`id` = `e`.`department_id`
WHERE `b`.`date` = '2019-06-20'
AND `b`.`is_delete` =0
ORDER BY `b`.`date` desc