Combining 2 event tables

I have 2 events tracking tables:

First table:

Second table:

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:

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.

>Solution :

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;