Check IN Monthly Join Table
Tue Aug 29 2023 03:55:16 GMT+0000 (Coordinated Universal Time)
Saved by @Taimoor
SELECT `source`.`employee` AS `employee`, `source`.`employee_name` AS `employee_name`, `TabEmployee`.`department` AS `Department`, `TabEmployee`.`designation` AS `Designation`, `TabEmployee`.`branch` AS `Branch`, `source`.`From Date` AS `From Date`, `source`.`To Date` AS `To Date`, `source`.`Day 1` AS `Day 1`, `source`.`Day 2` AS `Day 2`, `source`.`Day 3` AS `Day 3`, `source`.`Day 4` AS `Day 4`, `source`.`Day 5` AS `Day 5`, `source`.`Day 6` AS `Day 6`, `source`.`Day 7` AS `Day 7`, `source`.`Day 8` AS `Day 8`, `source`.`Day 9` AS `Day 9`, `source`.`Day 10` AS `Day 10`, `source`.`Day 11` AS `Day 11`, `source`.`Day 12` AS `Day 12`, `source`.`Day 13` AS `Day 13`, `source`.`Day 14` AS `Day 14`, `source`.`Day 15` AS `Day 15`, `source`.`Day 16` AS `Day 16`, `source`.`Day 17` AS `Day 17`, `source`.`Day 18` AS `Day 18`, `source`.`Day 19` AS `Day 19`, `source`.`Day 20` AS `Day 20`, `source`.`Day 21` AS `Day 21`, `source`.`Day 22` AS `Day 22`, `source`.`Day 23` AS `Day 23`, `source`.`Day 24` AS `Day 24`, `source`.`Day 25` AS `Day 25`, `source`.`Day 26` AS `Day 26`, `source`.`Day 27` AS `Day 27`, `source`.`Day 28` AS `Day 28`, `source`.`Day 29` AS `Day 29`, `source`.`Day 30` AS `Day 30`, `source`.`Day 31` AS `Day 31` FROM (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`, MAX(CASE WHEN DAY(`time`) = 1 THEN `min` END) AS `Day 1`, MAX(CASE WHEN DAY(`time`) = 2 THEN `min` END) AS `Day 2`, MAX(CASE WHEN DAY(`time`) = 3 THEN `min` END) AS `Day 3`, MAX(CASE WHEN DAY(`time`) = 4 THEN `min` END) AS `Day 4`, MAX(CASE WHEN DAY(`time`) = 5 THEN `min` END) AS `Day 5`, MAX(CASE WHEN DAY(`time`) = 6 THEN `min` END) AS `Day 6`, MAX(CASE WHEN DAY(`time`) = 7 THEN `min` END) AS `Day 7`, MAX(CASE WHEN DAY(`time`) = 8 THEN `min` END) AS `Day 8`, MAX(CASE WHEN DAY(`time`) = 9 THEN `min` END) AS `Day 9`, MAX(CASE WHEN DAY(`time`) = 10 THEN `min` END) AS `Day 10`, MAX(CASE WHEN DAY(`time`) = 11 THEN `min` END) AS `Day 11`, MAX(CASE WHEN DAY(`time`) = 12 THEN `min` END) AS `Day 12`, MAX(CASE WHEN DAY(`time`) = 13 THEN `min` END) AS `Day 13`, MAX(CASE WHEN DAY(`time`) = 14 THEN `min` END) AS `Day 14`, MAX(CASE WHEN DAY(`time`) = 15 THEN `min` END) AS `Day 15`, MAX(CASE WHEN DAY(`time`) = 16 THEN `min` END) AS `Day 16`, MAX(CASE WHEN DAY(`time`) = 17 THEN `min` END) AS `Day 17`, MAX(CASE WHEN DAY(`time`) = 18 THEN `min` END) AS `Day 18`, MAX(CASE WHEN DAY(`time`) = 19 THEN `min` END) AS `Day 19`, MAX(CASE WHEN DAY(`time`) = 20 THEN `min` END) AS `Day 20`, MAX(CASE WHEN DAY(`time`) = 21 THEN `min` END) AS `Day 21`, MAX(CASE WHEN DAY(`time`) = 22 THEN `min` END) AS `Day 22`, MAX(CASE WHEN DAY(`time`) = 23 THEN `min` END) AS `Day 23`, MAX(CASE WHEN DAY(`time`) = 24 THEN `min` END) AS `Day 24`, MAX(CASE WHEN DAY(`time`) = 25 THEN `min` END) AS `Day 25`, MAX(CASE WHEN DAY(`time`) = 26 THEN `min` END) AS `Day 26`, MAX(CASE WHEN DAY(`time`) = 27 THEN `min` END) AS `Day 27`, MAX(CASE WHEN DAY(`time`) = 28 THEN `min` END) AS `Day 28`, MAX(CASE WHEN DAY(`time`) = 29 THEN `min` END) AS `Day 29`, MAX(CASE WHEN DAY(`time`) = 30 THEN `min` END) AS `Day 30`, MAX(CASE WHEN DAY(`time`) = 31 THEN `min` END) AS `Day 31` 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) `source` LEFT JOIN `tabEmployee` `TabEmployee` ON `source`.`employee` = `TabEmployee`.`employee` LIMIT 1048575
Comments