MySQL – Display All Days of the Month with or without Values

MySQLmysql-5.6

I am trying to generate a display of time_in and time_out of employees. One pair in the morning and another pair in the afternoon. The query below only shows the dates that has values in it.

QUERY

SELECT empID, emp_day, 
MAX(am_in1) as am_in,
MAX(am_out1) as am_out, 
MAX(pm_in1) as pm_in, 
MAX(pm_out1) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in' 
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_in1,
  CASE WHEN am_pm='am' AND in_out='out' 
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_out1,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_in1,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_out1
  FROM attendance
) as o
GROUP BY empID, emp_day

WORKING OUTPUT

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out    
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00

How can I join the query below…

QUERY THAT GENERATES DAYS OF THE CURRENT MONTH

SELECT date_field
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
) as AB WHERE MONTH(date_field) = MONTH(NOW())

…to come up with the following output?

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out    
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  2          |             |              |             |  
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  4          |             |              |             |  

...until the end of the current month

152     |  31         |             |              |             |

Below are the tables and and sample values I am working with.

TABLE DEFINITION AND VALUES

CREATE TABLE IF NOT EXISTS `attendance` (
  `empID` int(11) NOT NULL,
  `time_stamp` datetime NOT NULL,
  `in_out` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `am_pm` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL
);

INSERT INTO `attendance` (`empID`, `time_stamp`, `in_out`, `am_pm`) VALUES
(152, '2017-01-01 12:00:00', 'out', 'am'),
(152, '2017-01-01 08:00:00', 'in', 'am'),
(152, '2017-01-03 12:00:00', 'out', 'am'),
(152, '2017-01-03 08:00:00', 'in', 'am'),
(152, '2017-01-01 05:00:00', 'out', 'pm'),
(152, '2017-01-01 01:00:00', 'in', 'pm'),
(152, '2017-01-03 05:00:00', 'out', 'pm'),
(152, '2017-01-03 01:00:00', 'in', 'pm');

Working Code: http://rextester.com/SVC13416

Best Answer

Here are the joined queries:

SELECT d.empID, DATE_FORMAT(d.date_field,'%e') as day_of_month, d.date_field, o.am_in, o.am_out, o.pm_in, o.pm_out
FROM (
SELECT e.empID, emp_day, 
MAX(e.am_inA) as am_in,
MAX(e.am_outB) as am_out, 
MAX(e.pm_inC) as pm_in, 
MAX(e.pm_outD) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in' 
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_inA,
  CASE WHEN am_pm='am' AND in_out='out' 
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_outB,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_inC,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_outD
  FROM attendance
  WHERE MONTH(`time_stamp`) = MONTH(NOW()) AND YEAR(`time_stamp`) = YEAR(NOW()) AND empID = 152
) as e
GROUP BY empID, emp_day) as o
RIGHT OUTER JOIN 
(SELECT date_field, `employee`.empID
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
   ) as AB, `employee` 
   WHERE MONTH(date_field) = MONTH(NOW()) AND YEAR(date_field) = YEAR(NOW()) AND empID = 152
) as d
ON o.emp_day = DATE_FORMAT(d.date_field,'%e') AND o.empID = d.empID
ORDER BY d.date_field;

And your fiddle: http://rextester.com/VRTYM92242

Related Question