1

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?

  • Your dataset have no any ordering info. So "first"-"last"-"previous"-"next" terms not exists. So the task is unsolvable. – Akina Nov 20 '18 at 17:44
  • @Akina Well I guess one could order by coalesce(start_data, end_date) or the equivalent in the OP's DBMS of choice, which he sadly failed to mention. – mustaccio Nov 20 '18 at 17:48
2

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.