3

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, 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

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.?

2

It's a bit convoluted, but I think this gets the result you're after:

;WITH src AS 
(
SELECT et.M_ID
    , et.Y_ID
    , et.T_ID
    , et.E_ID
    , et.BeginDate
    , Marker = CASE WHEN et.BeginDate <= DATEADD(DAY, 1, LAG(et.EndDate, 1, et.BeginDate) OVER (PARTITION BY et.M_ID
            , et.Y_ID
            , et.T_ID
            , et.E_ID
             ORDER BY et.BeginDate))
        THEN 1
        ELSE 0
        END
    , et.EndDate
FROM #EventTable et
)
, src2 AS 
(
SELECT src.*
    , rn1 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate)
    , rn2 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate DESC)
FROM src
)
SELECT src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate = MIN(src2.BeginDate)
    , EndDate = MAX(src2.EndDate)
FROM src2
WHERE src2.rn1 = 1
    OR src2.rn2 = 1
GROUP BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , src2.Marker
ORDER BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate
    , EndDate;

Output:

╔════════╦══════╦══════╦══════╦════════════╦════════════╗
║  M_ID  ║ Y_ID ║ T_ID ║ E_ID ║ StartDate  ║  EndDate   ║
╠════════╬══════╬══════╬══════╬════════════╬════════════╣
║ 134560 ║    5 ║    8 ║    3 ║ 2017-04-01 ║ 2017-08-31 ║
║ 135709 ║    2 ║    7 ║    1 ║ 2017-01-01 ║ 2017-06-30 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-01-01 ║ 2017-05-31 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-07-01 ║ 2017-08-31 ║
║ 135709 ║    5 ║    8 ║    1 ║ 2017-02-01 ║ 2017-07-31 ║
╚════════╩══════╩══════╩══════╩════════════╩════════════╝

The code uses the LAG() aggregate to inspect the next logical row to see if the start date is prior to or adjacent-by-one-day to the end date in the current row. It then uses the two ROW_NUMBER() aggregates to get the first-and-last rows of each group.

  • Hi @Max, When There are Gaps between Events of the same M_ID, then They should not be grouped and left as it is... – Thomas Newton Apr 18 at 18:43
  • Unless I'm misunderstanding, that's what this code does. – Max Vernon Apr 18 at 18:51
  • Hi @Max, This is Regarding Finding the Continuous Date Ranges. I had added some more sample Data for this. You can also find at dbfiddle.uk/… – Thomas Newton Apr 18 at 19:32
  • I had added the Code here: dbfiddle.uk/…. Please Check. This gets the Output correctly but needs optimization. – Thomas Newton Apr 19 at 16:59
1

If you're working with SQL 2012 or later, you can use a LEAD function call (i.e., LEAD([BeginDate],1) OVER (PARTITION BY [M_ID],[Y_ID],[T_ID],[E_ID]) ORDER BY [BeginDate]). That way, you can compare the current row EndDate with the next row BeginDate.

  • I tried using gaps and Islands concept also., but failed in further cases. – Thomas Newton Apr 18 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.