davidinho davidinho - 1 month ago 15
SQL Question

Complex Self-Join

I have a problem with a view, I'm working with Sql Server.
I have a table like this:

+-------+------+
| Start | End |
+-------+------+
| 1 | Null |
| 3 | 4 |
| 6 | 9 |
+-------+------+


This table represents a series of timeframe, if End is Null this means that it has not finished, but there may be short breaks (3-4 and 6-9), I would like to create a view that will show all the timeframes like this:

+-------+------+
| Start | End |
+-------+------+
| 1 | 3 |
| 3 | 4 |
| 4 | 6 |
| 6 | 9 |
| 9 | Null |
+-------+------+


I can't find a solution. I tried more than than an hour with no results.

Answer

I think you want union all with lead():

select start, lead(start) over (order by start)
from ((select t.start as start from likethis t
      ) union all
      (select t.end from likethis t
      )
     ) t
where start is not null
order by start;