eM9zcy01

1SELECT empID, emp_day, 
2MAX(am_in1) as am_in,
3MAX(am_out1) as am_out, 
4MAX(pm_in1) as pm_in, 
5MAX(pm_out1) as pm_out
6FROM (
7  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
8  CASE WHEN am_pm='am' AND in_out='in' 
9    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_in1,
10  CASE WHEN am_pm='am' AND in_out='out' 
11    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_out1,
12  CASE WHEN am_pm='pm' AND in_out='in'
13    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_in1,
14  CASE WHEN am_pm='pm' AND in_out='out'
15    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_out1
16  FROM attendance
17) as o
18GROUP BY empID, emp_day
19	
20empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out    
21152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
22152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
23	
24empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out    
25152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
26152     |  2          |             |              |             |  
27152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
28152     |  4          |             |              |             |  
29
30...until the end of the current month
31
32152     |  31         |             |              |             |
33	
34CREATE TABLE `days`
35( d INT NOT NULL PRIMARY KEY ) ;
36
37INSERT INTO `days`
38VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
39(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),
40(27),(28),(29),(30);
41
42CREATE TABLE IF NOT EXISTS `attendance` (
43  `empID` int(11) NOT NULL,
44  `time_stamp` datetime NOT NULL,
45  `in_out` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
46  `am_pm` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL
47) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
48
49INSERT INTO `attendance` (`empID`, `time_stamp`, `in_out`, `am_pm`) VALUES
50(152, '2016-01-01 12:00:00', 'out', 'am'),
51(152, '2016-01-01 08:00:00', 'in', 'am'),
52(152, '2016-01-03 12:00:00', 'out', 'am'),
53(152, '2016-01-03 08:00:00', 'in', 'am'),
54(152, '2016-01-01 05:00:00', 'out', 'pm'),
55(152, '2016-01-01 01:00:00', 'in', 'pm'),
56(152, '2016-01-03 05:00:00', 'out', 'pm'),
57(152, '2016-01-03 01:00:00', 'in', 'pm');