Get date range gaps from a date set

Question

Here is the set we have

Date
-----------
2018-04-01
2018-04-02
2018-04-18
2018-04-19
2018-04-21
2018-04-22
2018-04-23
2018-04-24
2018-04-25
2018-04-26
2018-04-27
2018-04-28
2018-04-29
2018-05-05
2018-05-06
2018-05-07
2018-05-08
2018-05-09
2018-05-28
2018-05-29
2018-05-30
2018-05-31

we would like to have missing date range in each month. So the output will be something like this:

start_date    end_date
------------------------
2018-04-03    2018-04-17
2018-04-20    2018-04-20
2018-04-30    2018-04-30
2018-05-01    2018-05-04
2018-05-10    2018-05-27

Please note we have missing dates between 2018-04-30 till 2018-05-04 but the output should be in each month range separately.

Thanks.

Answer 1

This is a classic gaps and islands problem. I chose to solve this the old school way, using correlated subqueries:

WITH cte AS (
    SELECT t1.Date, ROW_NUMBER() OVER (ORDER BY t1.Date) rn,
    FROM yourTable t1
    WHERE NOT EXISTS (SELECT TOP 1 t2.Date FROM yourTable t2
                      WHERE t2.Date = DATEADD(day, -1, t1.Date)) OR
          NOT EXISTS (SELECT TOP 1 t2.Date FROM yourTable t2
                      WHERE t2.Date = DATEADD(day, 1, t1.Date))
)

SELECT
    DATEADD(day, 1, MIN(Date)) AS start_date,
    DATEADD(day, -1, MAX(Date)) AS end_date
FROM cte
WHERE rn > 1
GROUP BY rn / 2
HAVING MIN(Date) <> MAX(Date);

Demo

Answer 2

If you simply want without the month split, you can try like following.

SELECT Dateadd(day, 1, [date])       start_date, 
       Dateadd(day, tbg - 1, [date]) end_date 
FROM   (SELECT *, 
               Datediff(day, [date], Lead([date]) OVER (ORDER BY [date])) TBG 
        FROM   @Date) T 
WHERE  T.tbg > 1 

Output (Without Considering overlapping months)

+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2018-04-03 | 2018-04-17 |
+------------+------------+
| 2018-04-20 | 2018-04-20 |
+------------+------------+
| 2018-04-30 | 2018-05-04 |
+------------+------------+
| 2018-05-10 | 2018-05-27 |
+------------+------------+

If you want to split the row which is overlapping between months, you can try like following.

;WITH cte 
     AS (SELECT *, 
                Datediff(month, start_date, end_date) md 
         FROM   (SELECT Dateadd(day, 1, [date])       start_date, 
                        Dateadd(day, tbg - 1, [date]) end_date 
                 FROM   (SELECT *, 
                                Datediff(day, [date], Lead([date]) 
                                                        OVER ( 
                                                          ORDER BY [date])) TBG 
                         FROM   @Date) T 
                 WHERE  T.tbg > 1) t2) 
SELECT Cast(start_date AS DATE) start_date, 
       Cast(end_date AS DATE)   end_date 
FROM   (SELECT start_date, 
               end_date 
        FROM   cte C1 
        WHERE  md = 0 
        UNION ALL 
        SELECT CASE 
                 WHEN t.n = 1 THEN start_date 
                 ELSE Dateadd(mm, Datediff(mm, 0, end_date), 0) 
               END AS start_date, 
               CASE 
                 WHEN t.n = 2 THEN end_date 
                 ELSE Dateadd (dd, -1, Dateadd(mm, Datediff(mm, 0, start_date) + 
                                                   1, 0)) 
               END AS end_date 
        FROM   cte c2 
               CROSS JOIN (SELECT 1 AS n 
                           UNION 
                           SELECT 2 AS n) t 
        WHERE  md > 0) t1 
        order by start_date

Online Demo

Output

+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2018-04-03 | 2018-04-17 |
+------------+------------+
| 2018-04-20 | 2018-04-20 |
+------------+------------+
| 2018-04-30 | 2018-04-30 |
+------------+------------+
| 2018-05-01 | 2018-05-04 |
+------------+------------+
| 2018-05-10 | 2018-05-27 |
+------------+------------+