I am trying to query a 2012 SQL-Server about some data.

Data is in the following table

What I want to get is the first date in sequential block of dates. These blocks in the example provided would be row 1->2, row 3->4, and row 5->6. Where I would want to return the from_date column in row 5 as it is the first date in the last block of sequential dates.

I have tried various approaches but I am not that familiar with SQL so I was hoping anyone here could point me in the right direction. Thanks in advance!

CodePudding user response：

WITH
  gaps AS
(
  SELECT
    *,
    CASE WHEN from_date = LEAD(to_date) OVER (ORDER BY from_date DESC) THEN 0 ELSE 1 END  AS is_first_row_of_group
  FROM
    your_table
)
SELECT TOP (1)
  from_date
FROM
  gaps
WHERE
  is_first_row_of_group = 1
ORDER BY
  from_date DESC

CodePudding user response：

You can compare From_Date and To_date using Lead, your query would be:

SELECT TOP 1 *
FROM (
    SELECT *
        ,DATEDIFF(DAY, To_Date, LEAD(From_Date) OVER (
                ORDER BY From_Date DESC
                )) AS Days_To_Next
    FROM #Table
)a
WHERE ISNULL(Days_To_Next,0) > 0
ORDER BY From_date ASC

CodePudding user response：

Presumably you could have many consecutive dates in a row, not just two? A bit convoluted but first identify consecutive dated rows, then assign a grouping to each with the latest dates having the lowest group, then select the min dates for group #1:

with cons as (
    select *, case when lead(from_date) over(order by from_date) = to_date then 1 end Cons
    from t
), gp as (
    select * , Row_Number() over (order by from_date desc) 
      - Row_Number() over (partition by cons order by from_date desc) gp
    from cons
)
select Min(from_date) from_date
from gp 
where gp = 1;

Example Fiddle