Base Query to Date Difference Between Overlapping and Non-Overlapping Dates

CREATE TABLE dbo.Projects

( projectid  INT          NOT NULL,

  title      VARCHAR(100) NOT NULL,

  start_date DATE         NOT NULL,

  end_date   DATE         NOT NULL);

Go

INSERT INTO dbo.Projects(projectid, title, start_date, end_date) VALUES

  (1, 'Project 1', '20100212', '20100220'),

  (2, 'Project 2', '20100214', '20100312'),

  (3, 'Project 3', '20100124', '20100201'),

  (4, 'Project 4', '20100401', '20100410'),

  (5, 'Project 5', '20100420', '20100425'),

  (6, 'Project 6', '20100501', '20100509'),

  (7, 'Project 7', '20100409', '20100415'),

  (8, 'Project 8', '20100414', '20100421'),

  (9, 'Project 9', '20100605', '20100610'),

  (10, 'Project 10', '20100614', '20100621'),

  (11, 'Project 11', '20100601', '20100625');

Go

והשליפה הרקורסיבית:

With T1 As

(Select    Row_Number() Over(Order By Start_date) N,

        *

From    Projects),

T2 As

(Select 1 N,

        start_date,

        end_date

From    T1

Where    N=1

Union All

Select    T2.N+1,

        T2.start_date,

        Case When T1.end_date>=T2.end_date Then T1.end_date Else T2.end_date End

From    T1

Inner Join T2

        On T2.End_date >= T1.start_date

Where    T1.N=T2.N+1

Union All

Select    T2.N+1,

        T1.start_date,

        T1.end_date

From    T1

Inner Join T2

        On T2.End_date < T1.start_date

Where    T1.N=T2.N+1

)

Select    start_date,

        Max(end_date) end_date

From    T2

Group By start_date;

הפלט המתקבל:

start_date    end_date

2010-01-24    2010-02-01

2010-02-12    2010-03-12

2010-04-01    2010-04-25

2010-05-01    2010-05-09

2010-06-01    2010-06-25