Retrieve bounded time periods as a step function where beginning of period returns 1, end of period returns 0

Question

I have a table in Microsoft SQL Server called Downtime that looks like this (I have omitted some irrelevant columns and many entries):

ID	StartTime	EndTime
5	2024-03-27 09:07:20.653	2024-03-27 09:09:07.690
17	2024-03-27 09:59:50.557	2024-03-27 10:59:50.137
24	2024-03-27 11:04:07.497	2024-03-27 11:07:02.657

I need to write a query that turns the above data into this format:

t_stamp	CurrentlyDown
2024-03-27 09:07:20.653	1
2024-03-27 09:09:07.690	0
2024-03-27 09:59:50.557	1
2024-03-27 10:59:50.137	0
2024-03-27 11:04:07.497	1
2024-03-27 11:07:02.657	0

In words, this query should split each original entry into two entries (one t_stamp for StartTime and one t_stamp for EndTime) and return a value (CurrentlyDown) of 1 (if t_stamp is from the StartTime column) or 0 (if t_stamp is from the EndTime column).

I can think to try two things:

• A self join around the ID field with a CASE statement checking the timestamp fields
• Two CTE's (one focused on grabbing StartTimes and the other focused on EndTimes) with a final query to join these two CTE's together around the ID column. Maybe just one CTE is needed here?

I am concerned with performance so I want to do this as efficiently as possible. I am far from a SQL expert, so I don't really know which path is best to take (if either).

Answer 1

Here is one option using CROSS APPLY

Example

Select B.* 
 From  YourTable A
 Cross Apply ( values (StartTime,1)
                     ,(EndTime  ,0)
             ) B(t_stamp,currentlydown)

Results

t_stamp                   currentlydown
2024-03-27 09:07:20.653   1
2024-03-27 09:09:07.690   0
2024-03-27 09:59:50.557   1
2024-03-27 10:59:50.137   0
2024-03-27 11:04:07.497   1
2024-03-27 11:07:02.657   0