I have a source table including column StartTime, EndTime, and a couple of other columns as you can see below.
My goal is to create a query that returns a dataset in which StartTime and EndTime are merged into a single Time column (so that each record appears twice, once with the StartTime as Time, once with the EndTime as Time).
I can easily achieve this with a subquery returning StartTime for the Time column, another subquery returning the EndTime for the Time column, the merge the results by UNION ALL. However, I can’t seem to figure out how to sort the new dataset so that the order is
- This record, StartTime as Time
- This record, EndTime as Time
- Next record, StartTime as Time
- Next record, EndTime as Time
and so on……
Your help would be greatly appreciated!
Sample below – Thank you!
Source Table: (LogID is an auto-increment PK in no particular order, so it can’t be used for sorting)
| LogID | Description | StartTime | EndTime |
|---|---|---|---|
| 1 | Travel to new site | 2019-7-31 05:30 | 2019-07-31 06:30 |
| 2 | Meeting | 2019-07-31 06:30 | 2019-07-31 07:00 |
| 3 | Presentation | 2019-07-31 07:00 | 2019-07-31 7:30 |
| 4 | Travel to new site | 2019-07-31 7:30 | 2019-07-31 12:00 |
| 5 | Setup | 2019-07-31 12:00 | 2019-07-31 13:15 |
Desired Result:
| LogID | Description | Time |
|---|---|---|
| 1 | Travel to new site | 2019-7-31 05:30 |
| 1 | Travel to new site | 2019-07-31 06:30 |
| 2 | Meeting | 2019-07-31 06:30 |
| 2 | Meeting | 2019-07-31 07:00 |
| 3 | Presentation | 2019-07-31 07:00 |
| 3 | Presentation | 2019-07-31 7:30 |
| 4 | Travel to new site | 2019-07-31 7:30 |
| 4 | Travel to new site | 2019-07-31 12:00 |
| 5 | Setup | 2019-07-31 12:00 |
| 5 | Setup | 2019-07-31 13:15 |
