How to make a mysql view for schedules of multiple people?

I have a table of data to describe a person's scheduled time. I开发者_开发问答t looks like this

Name | day | start_hour | end_hour
-
Tom | Monday   | 8  | 12
Tom | Weekend  | 16 | 20
Dick| Weekdays | 9  | 15
Sue | Sunday   | 7  | 23

How can I make a view that looks like this?

Hr | M         | Tu   | W    | Th  | F    | Sa | Su
...
8  | Tom       |      |      |     |      |    | 
9  | Tom, Dick | Dick | Dick | Dick| Dick |    | 
...

A view of each hour of each day and who is scheduled during that time.

If you have the hours table (1 integer column hour, values 0-23):

SELECT
   hour,
   GROUP_CONCAT(DISTINCT IF(day='Monday'    OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Tuesday'   OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Wednesday' OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Thursday'  OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Friday'    OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Saturday'  OR day='Weekend', name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Sunday'    OR day='Weekend', name,null) SEPARATOR ',')
FROM hours 
LEFT JOIN schedule
ON start_hour <= hour AND end_hour > hour OR end_hour = 0
GROUP BY hour;

I would try something like this query. It would give you how many guys could start to work at start_hour

start_hour, monday, tuesday 8 2 3

SELECT start_hour, SUM(CASE DAY='Moday' THEN 1 END),
        SUM(CASE DAY='Tuesday' THEN 1 END),
        SUM(CASE DAY='Wednesday' THEN 1 END),
 FROM schedule,
 (
 SELECT 'Monday' dayname FROM dual
 UNION
 SELECT 'Tuesday' dayname FROM dual
 UNION
 SELECT 'Wednesday' dayname FROM dual) days,
 WHERE  days.dayname=schedule.dayname
 GROUP BY start_hour

SELECT start_hour, 
        GROUP_CONCAT( (CASE DAY='Moday' THEN null ELSE Name  END),
        GROUP_CONCAT( (CASE DAY='Tuesday' THEN null ELSE Name  END),
        GROUP_CONCAT( (CASE DAY='Wednesday' THEN null ELSE Name  END),
 FROM schedule,
 (
 SELECT 'Monday' dayname FROM dual
 UNION
 SELECT 'Tuesday' dayname FROM dual
 UNION
 SELECT 'Wednesday' dayname FROM dual) days,
 WHERE  days.dayname=schedule.dayname
 GROUP BY start_hour