0

I have a table: 'Project'

CREATE TABLE [dbo].[Project](
    [Task_ID] [int] NULL,
    [Start_Date] [date] NULL,
    [End_Date] [date] NULL
) ON [PRIMARY]
GO

The data in this table is --

1   2015-10-01  2015-10-02
2   2015-10-02  2015-10-03
3   2015-10-03  2015-10-04
4   2015-10-13  2015-10-14
5   2015-10-14  2015-10-15
6   2015-10-28  2015-10-29
7   2015-10-30  2015-10-31

The difference between Start_Date and End_Date is guaranteed 1 Day. I need to find the missing gaps between these data sequences.

Desired Output:

2015-10-05  2015-10-12
2015-10-16  2015-10-27
2015-10-29  2015-10-30

I was able to get this much so far --

SELECT [Start_Date]
    ,MIN([End_Date]) As [End_Date]
    ,DATEDIFF(DAY, [Start_Date], MIN([End_Date])) As Duration
FROM (
    SELECT [Start_Date]
    FROM Project
    WHERE [Start_Date] NOT IN (
            SELECT [End_Date]
            FROM Project
            )
    ) A
    ,(
        SELECT [End_Date]
        FROM Project
        WHERE [End_Date] NOT IN (
                SELECT [Start_Date]
                FROM Project
                )
        ) B
WHERE [Start_Date] < [End_Date]
GROUP BY [Start_Date]

which is giving me this --

Start_Date  End_Date    Duration
2015-10-01  2015-10-04  3
2015-10-13  2015-10-15  2
2015-10-28  2015-10-29  1
2015-10-30  2015-10-31  1

Any suggestion how to carry on from here. I'm really stuck here. Thanks in advance.

Maverick Sachin
  • 874
  • 7
  • 12
  • 1
    which version of sql-server you are using? – TriV Jun 26 '17 at 08:08
  • 1
    imo, this was clearly answered here: https://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges – nico boey Jun 26 '17 at 08:39
  • Possible duplicate of [SQL Query to show gaps between multiple date ranges](https://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges) – Hybris95 Jun 27 '17 at 13:19

0 Answers0