Possible to sort via two time stamps and display same row twice.

Question

I'm looking at creating a time table solution. I have a task sheet that looks like

Area 1 item 1 startTime endTime
Area 1 item1 startTime endTime

I wish to create a display where I can view what even is happening next, either endTime or startTime

i.e.

Newcastle reel 16:45 18:45
Newcastle reel2 17:45 19:45

would output

Newcastle reel 16:45
Newcastle reel 17:45
Newcastle reel 18:45
Newcastle reel 19:45

More so, I would like to detect if the time is a startTime or an endTime would I have to enter two rows for each activity (time,area,item, start|end). I can make the interface to the creation of two rows. I just wondered if there was a better solution.
Any input is appreciated,
Thanks,
Jamie

Answer 1

You could have an event table with

• id
• time
• type (start or end)
• event details id

and an event details table with

• id
• description

You can then do select time, type, description from event left join event_details on event.details_id = event_details.id order by time.

This should give you the desired output.

If you want to get output with start AND end times in each row, then you need to do something like select event_details.description, min(time) as start, max(time) as end from event left join event_details on event.details_id = event_details.id group by event_details.description.

Answer 2

With your existing schema, this query should work:

SELECT * FROM
((SELECT Area, Item, startTime AS eventTime FROM tasks)
UNION
(SELECT Area, Item, endTime FROM tasks)) AS t
ORDER BY eventTime

Basically, you're selecting all the start times, then the end times, then sorting them.