Combining 2 event tables-CodePudding

I have 2 events tracking tables:

First table:

ID	Event_Name	Event_Date
1111	xxxxxxxxxx	2019-12-01

Second table:

ID	Event_Name	Event_Date
1111	yyyyyyyyyy	2019-12-02

The tables are of 2 different event names,first table has event name 'xxxxxxxxxx' and 2nd has 'yyyyyyyyyy'. I would like to combine these 2, such that the resulting table is as below:

ID	Event_Name	Event_Start_Date	Event_End_Date
1111	xxxxxxxxxx	2019-12-01	2019-12-02
1111	yyyyyyyyyy	2019-12-02	2019-12-05

meaning the Event_Date of 'xxxxxxxx' is the Event_Start_Date of the'xxxxxxxx' then the Event_End_Date of event 'xxxxxxxxx' is the Event_Date of 'yyyyyyyy' and so on for the rest of the events. All the event names are known and finite.

CodePudding user response：

As mentioned in the comments, you can use UNION ALL here, and then LEAD to get the start date of the next event. As you don't explain where the date 2019-12-05 comes from, I just put it in as the value for LEAD to return if there isn't another row:

WITH Events AS(
    --I assume that the data types in the 2 tables are identical
    --If not CAST/CONVERT appropriately
    SELECT ID,
           Event_Name,
           Event_Date
    FROM dbo.FirstTable
    UNION ALL
    SELECT ID,
           Event_Name,
           Event_Date
    FROM dbo.SecondTable)
SELECT ID,
       Event_Name,
       Event_Date AS Event_Start_Date,
       LEAD(Event_Date,1,'20191205') OVER (ORDER BY EventDate ASC) AS Event_End_Date
FROM Events;