CODEWITHSUNDEEP
sql-servergaps-and-islands
Emanuel Isaia
Emanuel Isaia

Reputation: 23

Select Earliest Date Within a Range of Dates Before a Break Occurs

I have been trying to find a solution for getting the most recent start date from a series of date ranges. I have found similar topics on StackOverflow as well as other websites, but none of worked for my specific scenario.

Here are two examples of the data in my database:

Example 1

Start Date | End Date
-----------|-----------
8/26/2006  | 5/31/2016
6/1/2016   | 12/31/2017
1/1/2018   | NULL

For this example, I'm expecting the result of the query to be: 8/26/2006. This is because the start and end dates are continuous all the way back to the original start date.

Example 2

Start Date | End Date
-----------|-----------
7/6/2014   | 11/30/2014
1/1/2019   | NULL

For this example, I'm expecting the result of the query to be: 1/1/2019. This is because there is a break between 11/30/2014 and 1/1/2019.

I don't need a list of all of the dates or even the end dates returned. I just need the earliest start date before a break in the date ranges.

I'm guessing what I need is a recursive CTE to loop through the records, such as this:

WITH CTE AS
  (
    SELECT
      T1.StartDate
     ,T1.EndDate
    FROM
      ExampleTable AS T1
    LEFT JOIN
      ExampleTable AS T2
        ON
        T1.EmployeeID = T2.EmployeeID
          AND T1.StartDate - 1 = T2.EndDate
    WHERE
      T1.EmployeeID = @EmployeeID
    UNION ALL
    SELECT
      C.EmployeeID
     ,C.StartDate
     ,T2.EndDate
    FROM
      CTE AS C
    JOIN
      ExampleTable AS T2
        ON
        C.EmployeeID = T2.EmployeeID
          AND T2.StartDate - 1 = C.EndDate
  )
SELECT
  StartDate
 ,NULLIF(MAX(ISNULL(EndDate, '32121231')), '32121231') AS EndDate
FROM
  CTE
GROUP BY
  StartDate;

But no luck. It always returns all of the date ranges I listed in examples 1 or 2. Can anyone help please?

Upvotes: 2

Views: 76

Answers (1)

Thom A
Thom A

Reputation: 95561

This seems the simplest method to get the result:

SELECT TOP 1 StartDate
FROM YourTable
ORDER BY CASE WHEN LAG(EndDate) OVER (ORDER BY StartDate) = DATEADD(DAY,-1,StartDate) THEN 1 ELSE 0 END,
         StartDate DESC;

So, for your data:

WITH VTE AS(
    SELECT CONVERT(date, StartDate,101) AS StartDate,
           CONVERT(date, EndDate,101) AS EndDate
    FROM (VALUES('7/6/2014','11/30/2014'),
                ('1/1/2019',NULL)) V(StartDate, EndDate))
SELECT TOP 1 StartDate
FROM VTE
ORDER BY CASE WHEN LAG(EndDate) OVER (ORDER BY StartDate) = DATEADD(DAY,-1,StartDate) THEN 1 ELSE 0 END,
         StartDate DESC;

WITH VTE AS(
    SELECT CONVERT(date, StartDate,101) AS StartDate,
           CONVERT(date, EndDate,101) AS EndDate
    FROM (VALUES('8/26/2006','5/31/2016'),
                ('6/1/2016 ','12/31/2017'),
                ('1/1/2018 ',NULL)) V(StartDate, EndDate))
SELECT TOP 1 StartDate
FROM VTE
ORDER BY CASE WHEN LAG(EndDate) OVER (ORDER BY StartDate) = DATEADD(DAY,-1,StartDate) THEN 1 ELSE 0 END,
         StartDate DESC;

Upvotes: 1

Related Questions