Eq9L7DAv

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	
24SELECT date_field
25FROM
26(
27    SELECT MAKEDATE(YEAR(NOW()),1) +
28    INTERVAL (MONTH(NOW())-1) MONTH +
29    INTERVAL daynum DAY date_field
30    FROM
31    (
32        SELECT t*10+u daynum FROM
33        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
34        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
35        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
36        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
37    ) as AA
38) as AB WHERE MONTH(date_field) = MONTH(NOW())
39	
40empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out    
41152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
42152     |  2          |             |              |             |  
43152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
44152     |  4          |             |              |             |  
45
46...until the end of the current month
47
48152     |  31         |             |              |             |
49	
50CREATE TABLE IF NOT EXISTS `attendance` (
51  `empID` int(11) NOT NULL,
52  `time_stamp` datetime NOT NULL,
53  `in_out` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
54  `am_pm` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL
55);
56
57INSERT INTO `attendance` (`empID`, `time_stamp`, `in_out`, `am_pm`) VALUES
58(152, '2017-01-01 12:00:00', 'out', 'am'),
59(152, '2017-01-01 08:00:00', 'in', 'am'),
60(152, '2017-01-03 12:00:00', 'out', 'am'),
61(152, '2017-01-03 08:00:00', 'in', 'am'),
62(152, '2017-01-01 05:00:00', 'out', 'pm'),
63(152, '2017-01-01 01:00:00', 'in', 'pm'),
64(152, '2017-01-03 05:00:00', 'out', 'pm'),
65(152, '2017-01-03 01:00:00', 'in', 'pm');