How to use LEAD LAG-like functions on multiple columns simultaneously

window functions

Suppose I have the following table: 

start_data | end_date
----------------------
t1         | Null
Null       | t2
t3         | Null
Null       | t4
t5         | Null

The output should be like this:

start_data | end_date
----------------------
t1         | t2
t3         | t4
t5         | Null

notes:

  • the type of ti is date
  • ti < ti+1 holds true for all values.

pseudo code:
for every record i
xi <— start_date
yi <— end_date
zi <—- min(yi) && xi>yi
(xi,zi) should be the output for this record i

Is it solvable using native SQL?

Best Answer

I guess one could order by coalesce(start_data, end_date)

WITH cte AS (
SELECT COALESCE(start_data, end_date) dt, 
       ROW_NUMBER() OVER (ORDER BY COALESCE(start_data, end_date)) rn
FROM datatable
)
SELECT MIN(dt) start_data, MAX(dt) end_date
FROM cte
GROUP BY ROUND((rn-1)/2)

This query is a model. The final text is dependent by DBMS.

Maybe you will need to replace MIN/MAX with CASE WHEN rn%2 = [0|1]

Related Question