Tweeted twitter.com/StackDBAs/status/1119073255371964416
3 Added some more Sample Data for Analysis
source | link

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description hereenter image description here The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot. enter image description hereenter image description here

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot. enter image description here

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot. enter image description here

2 added 143 characters in body
source | link

How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here

Theenter image description here The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 MayApr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '30'31 JunAug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017');

The Required Output Dates are marked in(Marked as RED Text) is mentioned in the above Screenshot. Exampleenter image description here

Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly, For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

The Required Output is also mentioned in the ScreenshotCan Someone help me out.?

How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here

The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 May 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '30 Jun 2017');

The Output Dates are marked in RED Text. Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly, For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

The Required Output is also mentioned in the Screenshot.

How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot. enter image description here

Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly, For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

Can Someone help me out.?

1
source | link

Merge Only Events that Start immediately after the Previous Event without any Gap

How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart. enter image description here

The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 May 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '30 Jun 2017');

The Output Dates are marked in RED Text. Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly, For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

The Required Output is also mentioned in the Screenshot.