How do you join between two delta tables?

2
votes

I'm wondering if there is an obvious way of joining two tables that I'm overlooking.

If data is loaded into a table so that only the deltas are logged how does one go about joining different tables together? There is no obvious join between the tables because the dates aren't guaranteed to match 1:1 on dates/keys

Given the following simple structure:

BeginDate | EndDate | Id | Col1 | Col2 ...

Table A might contain the data:

1/1/2014 | 1/7/2014  | 1 | A | i
1/7/2014 | 1/15/2014 | 1 | B | i

Table B might contain the data:

1/1/2014 | 1/3/2014  | 1 | ABCDEF | 123
1/3/2014 | 1/8/2014  | 1 | FEDCBA | 321
1/8/2014 | 1/15/2014 | 1 | QWERTY | 314

How would you go about joining these two tables equally? My current approach has been to APPLY all of the tables against a calendar first to generate entries for each date and then to join those results on Date/Key but this is obviously quite inefficient.

Example desired output:

1/1/2014 | 1/3/2014  | 1 | A | i | ABCDEF | 123
1/3/2014 | 1/7/2014  | 1 | A | i | FEDCBA | 321
1/7/2014 | 1/8/2014  | 1 | B | i | FEDCBA | 321
1/8/2014 | 1/15/2014 | 1 | B | i | QWERTY | 314
2
sqlsql-servertsql

2 Answers

0
votes

I've got desired result via this query (note mysql syntax, you will need to change greatest/least to sql server analog)

select greatest(a.begin,b.begin), least(a.end, b.end), a.*, b.*
from table1 as a, table2 as b
where
a.begin <= b.end and a.end >= b.begin
order by 1, 2
0
votes

You can do a join based on the temporal intersection of the 2 tables, eg

SELECT 
    CASE WHEN t1.dt1 >= t2.dt1 THEN t1.dt1 ELSE t2.dt1 END AS dtStart,
    CASE WHEN t1.dt2 >= t2.dt2 THEN t1.dt2 ELSE t2.dt2 END AS dtEnd,
    t1.col1 t1Col1,
    t2.col1 t2Col1,
    t1.col2 t1Col2,
    t2.col2 t2Col2,
    t1.id t1ID,
    t2.id t2ID
FROM dbo.TestTable t1
JOIN dbo.TestTable2 t2 ON
 (t1.dt1 >= t2.dt1 AND t1.dt2 <= t2.dt2)
     OR (t2.dt1 >= t1.dt1 AND t2.dt2 <= t1.dt2)