SELECT `employee`, `employee_name`, DATE_FORMAT(MIN(`source`.`time`), '%d-%m-%Y') AS `From Date`, DATE_FORMAT(MAX(`source`.`time`), '%d-%m-%Y') AS `To Date`, -- Day 1 (Monday) MAX(CASE WHEN DAYOFWEEK(`time`) = 2 THEN `min` END) AS Monday, -- Day 2 (Tuesday) MAX(CASE WHEN DAYOFWEEK(`time`) = 3 THEN `min` END) AS Tuesday, -- Day 3 (Wednesday) MAX(CASE WHEN DAYOFWEEK(`time`) = 4 THEN `min` END) AS Wednesday, -- Day 4 (Thursday) MAX(CASE WHEN DAYOFWEEK(`time`) = 5 THEN `min` END) AS Thursday, -- Day 5 (Friday) MAX(CASE WHEN DAYOFWEEK(`time`) = 6 THEN `min` END) AS Friday, -- Day 6 (Saturday) MAX(CASE WHEN DAYOFWEEK(`time`) = 7 THEN `min` END) AS Saturday, -- Day 7 (Sunday) MAX(CASE WHEN DAYOFWEEK(`time`) = 1 THEN `min` END) AS Sunday FROM ( SELECT `employee`, `employee_name`, DATE(`time`) AS `time`, DAY(`time`) AS `day`, TIME_FORMAT(MIN(TIME(`time`)), '%H:%i') AS `min` FROM `tabEmployee Checkin` WHERE true [[AND `time` >= {{From_date}}]] [[AND `time` <= DATE_ADD({{To_date}}, INTERVAL 1 DAY)]] GROUP BY `employee`, `employee_name`, DATE(`time`) ) AS source GROUP BY `employee`, `employee_name` ORDER BY `employee` ASC, `employee_name` ASC;