SQL Server – Sort Records in Original Order After UNION ALL

sql serversql-server-2008-r2

My question seems to have been worded poorly the first time around, so this is my attempt to explain it more clearly + provide table structure and data below.

Please consider table dt:

Create Table dt
(LogID Int Not Null Identity Primary Key, StartTime DateTime, EndTime DateTime)

Insert Into dt(StartTime,EndTime)
Values 
    ('2019-07-31 07:00', '2019-07-31 7:30'),
    ('2019-07-31 06:30', '2019-07-31 07:00'),
    ('2019-07-31 12:00', '2019-07-31 13:15'),
    ('2019-07-31 7:30', '2019-07-31 12:00'),
    ('2019-7-31 05:30', '2019-07-31 06:30')
LogID StartTime EndTime
1 2019-07-31 07:00:00.000 2019-07-31 07:30:00.000
2 2019-07-31 06:30:00.000 2019-07-31 07:00:00.000
3 2019-07-31 12:00:00.000 2019-07-31 13:15:00.000
4 2019-07-31 07:30:00.000 2019-07-31 12:00:00.000
5 2019-07-31 05:30:00.000 2019-07-31 06:30:00.000

I'm trying to come up with a query that would return

  • each record twice, once with StartTime and once with EndTime as EventTime
  • Order by EventTime and then by StartTime first, EndTime second.

Desired result:

LOGID EVENTTIME
5 31/07/2019 5:30
5 31/07/2019 6:30
2 31/07/2019 6:30
2 31/07/2019 7:00
1 31/07/2019 7:00
1 31/07/2019 7:30
4 31/07/2019 7:30
4 31/07/2019 12:00
3 31/07/2019 12:00
3 31/07/2019 13:15

I have tried

Select 
LogID, StartTime As EventTime, 'Start' As EventType from dt
Union All
Select LogID, EndTime, 'End'from dt
Order By
EventTime ASC, EventType Desc

But the order is not correct

Thank you for your help!

Best Answer

You could use CROSS APPLY to unpivot the time values into a column (along with a description of each in another column). That would give you the two new columns, EventTime and EventType, but also keep all the original columns accessible. As a result, you would be able to sort the resulting set first by StartTime, then by EventTime, while limiting the output columns only to those required:

SELECT
  dt.LogID
, et.EventTime
, et.EventType
FROM
  dt
  CROSS APPLY
  (
    VALUES
      (StartTime, 'Start')
    , (EndTime  , 'End'  )
  ) AS et (EventTime, EventType)
ORDER BY
  dt.StartTime ASC,
  et.EventTime ASC
;

This way you do not need to use a derived table, nor do you need to hit the source table more than once.

See this fiddle for a live demonstration.

Related Question