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)
|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|
|1||Travel to new site||2019-7-31 05:30|
|1||Travel to new site||2019-07-31 06:30|
|4||Travel to new site||2019-07-31 7:30|
|4||Travel to new site||2019-07-31 12:00|