T-SQL, list of DATETIME, create <from> - <to> from it-CodePudding

What would I need to do to achieve the following? Somehow I can't seem to find a good solution.

I have a few CTEs, and the last one is producing just a list of DATETIME values, with a row number column, and those are ordered by the DATETIME.

For example

rn  datetime
---------------------------
1   2023-01-07 01:00:00.000
2   2023-01-08 05:30:00.000
3   2023-01-08 08:00:00.000
4   2023-01-09 21:30:00.000

How do I have to join this CTE with each other in order to get the following result:

from                        to
---------------------------------------------------
2023-01-07 01:00:00.000     2023-01-08 05:30:00.000
2023-01-08 08:00:00.000     2023-01-09 21:30:00.000

Doing a regular inner join (with t1.rn = t2.rn - 1) gives me one row too much (the one from 05:30 to 08:00). So basically each date can only be "used" once.

Hope that makes sense... thanks!

Inner joining the CTE with itself, which didn't return the wanted result.

CodePudding user response：

You can pivot the outcome of your CTE and distribute rows using arithmetics : modulo 2 comes to mind.

Assuming that your CTE returns columns dt (a datetime field) and rn (an integer row number) :

select min(dt) dt_from, max(dt) dt_to
from cte 
group by ( rn - 1 ) % 2

On T-SQL we could also leverage integer division to express the pair grouping:

group by ( rn - 1 ) / 2

CodePudding user response：

You can avoid both the JOIN and the GROUP BY by using LAG to retrieve a previous column value in the result set. The server may be able to generate an execution plan that iterates over the data just once instead of joining or grouping :

with pairs as (
    SELECT rn, lag(datetime) OVER(ORDER BY rn) as dt_from, datetime as dt_to
    from another_cte        
)
select dt_from,dt_to
from pairs
ORDER BY rn
where rn%2=0

The row number itself can be calculated from datetime :

with pairs as (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY datetime) AS rn,
        lag(datetime) OVER(ORDER BY datetime) as dt_from, 
        datetime as dt_to
    from another_cte  
)
select dt_from,dt_to
from pairs
ORDER BY dt_to
where rn%2=0