I need to create an SQL Query that will use the following data:
shift_id, emp_id, date, starttime, endtime
1 , 55 , 2015-10-14, 06:00:00, 10:00:00
2 , 55 , 2015-10-15, 03:00:00, 13:00:00
3 , 52 , 2015-10-15, 07:00:00, 14:00:00
Then re-arrange it so that it shows the 'date' on top as a column with the starting day being the NEXT Sunday. I have begun an attempt below but if there are 2 employees (emp_id) working on the same day, it does not display grouped:
SELECT concat(firstname,' ', surname) AS 'Fname',
(CASE WHEN DAYOFWEEK(date) = 1 THEN concat(starttime,'-', endtime) END) `Sunday`,
(CASE WHEN DAYOFWEEK(date) = 2 THEN concat(starttime,'-', endtime) END) `Monday`,
(CASE WHEN DAYOFWEEK(date) = 3 THEN concat(starttime,'-', endtime) END) `Tuesday`,
(CASE WHEN DAYOFWEEK(date) = 4 THEN concat(starttime,'-', endtime) END) `Wednesday`,
(CASE WHEN DAYOFWEEK(date) = 5 THEN concat(starttime,'-', endtime) END) `Thursday`,
(CASE WHEN DAYOFWEEK(date) = 6 THEN concat(starttime,'-', endtime) END) `Friday`,
(CASE WHEN DAYOFWEEK(date) = 7 THEN concat(starttime,'-', endtime) END) `Saturday`
FROM shifts NATURAL JOIN employees
GROUP BY Fname, date
Here is the result with showing the employee name twice instead of grouping? Where have I gone wrong?:
Using GROUP_CONCAT, the incorrect result shows as below: