1

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

0

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

  • it's an infinite loop in mysql :( – oozmac Jan 27 '17 at 1:22
  • if you have a lot of data in your database it might be useful to add more filters. see my updated answer to filter by month, year and employee ID. – moni_dragu Jan 27 '17 at 7:04
  • hi @moni_dragu!!! it worked, thank you so much!!! – oozmac Jan 28 '17 at 23:44
  • I'm glad I could help. – moni_dragu Jan 29 '17 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.