Combining date ranges of 2 tables to a single table

Question

Hopefully, a straightforward question (but with a difficult answer, I suspect). I have 2 tables with date ranges that cover the same "overall" range, but TABLE_A has the ranges broken down a bit more initially, while TABLE_B breaks it down more later:

CREATE TABLE #TABLE_A (
START_DT datetime,
END_DT datetime)

INSERT INTO #TABLE_A VALUES ('4/1/1993', '4/29/1993');
INSERT INTO #TABLE_A VALUES ('4/29/1993', '5/12/1993');
INSERT INTO #TABLE_A VALUES ('5/13/1993', '5/26/1993');
INSERT INTO #TABLE_A VALUES ('5/27/1993', '8/18/1993');
INSERT INTO #TABLE_A VALUES ('2/10/1994', '2/23/1994');
INSERT INTO #TABLE_A VALUES ('2/25/1994', '3/9/1994');
INSERT INTO #TABLE_A VALUES ('3/10/1994', '5/5/1994');

CREATE TABLE #TABLE_B (
START_DT datetime,
END_DT datetime)

INSERT INTO #TABLE_B VALUES ('4/1/1993', '5/12/1993');
INSERT INTO #TABLE_B VALUES ('5/13/1993', '8/18/1993');
INSERT INTO #TABLE_B VALUES ('2/10/1994', '2/22/1994');
INSERT INTO #TABLE_B VALUES ('2/24/1994', '3/23/1994');
INSERT INTO #TABLE_B VALUES ('3/24/1994', '4/20/1994');
INSERT INTO #TABLE_B VALUES ('4/21/1994', '5/5/1994');

What I want is a TABLE_C that combines these ranges:

START DT      END DT    
4/1/1993       4/29/1993      FROM TABLE A
4/29/1993      5/12/1993      FROM TABLE A
5/13/1993      5/26/1993      FROM TABLE A
5/27/1993      8/18/1993      FROM TABLE A
2/10/1994      2/22/1994      FROM TABLE A or TABLE B
2/23/1994       3/9/1994      FROM TABLE A
3/10/1994      3/23/1994      START DT FROM TABLE A, END DT FROM TABLE B
3/24/1994      4/20/1994      FROM TABLE B
4/21/1994       5/5/1994      FROM TABLE B

I'm open to any suggestions on how to create TABLE_C. I have looked at trying to use MERGE, SSIS, cursors, etc. but keep getting bogged down or figure there must be an easier way. Any suggestions on how to approach this? I looked in "Similar Questions" and I can't seem to find my particular problem addressed. If this basically has been asked, my apologies.

Thanks

Answer 1

You are not going to find a simple answer. You are going to have to make a custom function or procedure which incorporates the exact logic you want. Sorry. Imagine some soft of iteration based on start_dt and length of time for each tuple.