Create rows in sql join with overlapping date ranges

Question

I have two tables as below:

TableA

ID	link1	link2	DATE_FROM	DATE_TO
99	H	J	1999-01-01	2005-01-01

TableB

ID	link1	link2	DATE_FROM	DATE_TO
99	X	Y	2002-01-01	2008-01-01

And I want to query them and retreive the result below:

ID	A.link1	A.link2	B.link1	B.link2	DATE_FROM	DATE_TO
99	H	J			1999-01-01	2002-01-01
99	H	J	X	Y	2002-01-02	2005-01-01
99			X	Y	2005-01-02	2008-01-01

It seems like I should be able to do this with sql using some kind of between join and perhaps some unions but I can't quite wrap my head around it. Can anyone tell me how can this result be produced please? Thanks

Welcome to Stackoverflow. Please make sure you tag the DB you're using to get most accurate answer. — django-unchained, Feb 20 '22 at 20:55

score 0 · Answer 1 · answered Feb 20 '22 at 20:54

Here's a solution in PostGreSQL:

SELECT a."id",
       a."link1",
       a."link2",
       NULL          AS link1,
       NULL          AS link2,
       a."date_from",
       b."date_from" AS DATE_TO
FROM   tab1 a
       left join tab2 b
              ON a."id" = b."id"
UNION ALL
SELECT a."id",
       a."link1",
       a."link2",
       b."link1",
       b."link2",
       b."date_from" + 1,
       a."date_to"
FROM   tab1 a
       join tab2 b
         ON a."id" = b."id"
UNION ALL
SELECT a."id",
       NULL            AS link1,
       NULL            AS link2,
       b."link1",
       b."link2",
       b."date_to" + 1 AS DATE_FROM,
       a."date_to"
FROM   tab2 a
       left join tab1 b
              ON a."id" = b."id";

Here's SQL fiddle with output.

your SQL fiddle does not exactly match the desired result of the OP — Tanguy, Jul 21 '23 at 16:32

Create rows in sql join with overlapping date ranges

1 Answers1