This looks like a Packing Intervals problem.  Based on your expected output, I am using closed intervals.  That is, both endpoints are considered to be included in the interval, so...

Instead of repeatedly saying "two or more tables", I would phrase it as "two row sources".  The main benefit of this is to clarify that you are only ever considering...

This needs to be done in the presentation layer.  SQL queries and stored procedures are the data layer, not the presentation layer.  You need to do this in SSRS, Tableau,...

Also, many people hesitate to download files from the Internet.  You should create expected results in the same way that you created your sample data.

Drew

You've made a very common mistake when working with intervals.  You're comparing starts with starts and ends with ends.  Instead, you should be comparing whether each interval starts before the...

Your sample data does not include the value 1900-01-01, yet you expect it in your output.  Those rows are missing, because your sample data does not match your expected output. ...

This is a very basic attempt to solve your problem.  It does not match your expected data, but, as others have pointed out, your expected data may be missing rows. ...

Use ROW_NUMBER() rather than a loop.  You would set up your ROW_NUMBER() so that the latest record is row number 1 and historical records are 2+.  If you want more...

This is based on code by Itzik Ben-Gan.  It uses a cross apply and a sum windowed function to increment the open interval count for start times and decrement it...

The subqueries are disastrous for performance.  Here is a query that gives the same results (I also added the requested SORT_NBR column) with much better performance.

SELECT b.BASKET_ID
...

To select all rows from CustomerLeft change join precedence by parentheses:

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN (dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS...

Here is an alternative that a) does not use an OR and b) does not use a subquery.

WITH TempOrdered AS
(
    SELECT t.ID
  ...

Here is an alternate solution that doesn't require a CTE.  NOTE: I've used COUNT() instead of SUM(), because COUNT() never returns a NULL value, whereas SUM() might--and does for the...

I've had to ad an identity column (ID) so the table can be ordered by something:

DECLARE @T table (ID int IDENTITY(1, 1),  A int )

insert...

@michael-2, My understanding is that you cannot switch partitions if there are indexes that are not aligned with the partition.

@Alexp, Have you tried DISABLE/REBUILD instead of DROP/CREATE?

Drew