Teradata join with SCD type 2

Question

How to join transaction table and SCD table to get records from SCD according to date of transaction.

Select A.id, A.trans_dt, trans_amt, B.pmt_meth
from trans1 A
left join scd1 B
  on A.id=B.id

Now I need to get pmt_meth for each transaction. so pmt_meth for first 2 transaction should be "M" and for last transaction it should be "P"

This is creating duplicates due to join issue (i know that) but not sure how to match dates so that no duplicate will be created and I will get desired result

create table scd1 (id integer, pmt_meth varchar (10), start_dt date, end_dt date)

Insert into scd1(1, P, '2015/06/30', '2017/05/30')
Insert into scd1(1, M, '2017/05/30', '2019/07/31')
Insert into scd1(1, P, '2019/07/31', '2050/12/31')

Create table trans1 (id integer, trans_dt date, trans_amt float)
Insert into trans1 (1, '2019/07/25', 100)
Insert into trans1 (1, '2019/07/01', 120)
Insert into trans1 (1, '2019/07/31', 50)

Now I need to get pmt_meth for each transaction. so pmt_meth for first 2 transaction should be "M" and for last transaction it should be "P"

Select A.id, A.trans_dt, trans_amt, B.pmt_meth
from trans1 A
left join scd1 B
  on A.id=B.id

Expected result is

id trans_dt      trans_amt    pmt_meth
1  '2019/07/25'  100            M
1  '2019/07/01'  120            M
1  '2019/07/31'  50             P

score 0 · Answer 1 · answered Aug 22 '19 at 14:22

0

Just include the date condition in the on clause:

Select A.id, A.trans_dt, A.trans_amt,
       B.pmt_meth      
from trans1 A left join
     scd1 B
     on A.id = B.id and
        A.trans_dt bewteen b.start_dt and b.end_dt;

answered Aug 22 '19 at 14:22

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Aug 22 '19 at 14:50

Your implementation is based on an inclusive-exclusive interval:

Select A.id, A.trans_dt, trans_amt, B.pmt_meth
from trans1 A
left join scd1 B
  on A.id=B.id
 and A.trans_dt >= B.start_dt 
 and A.trans_dt < B.end_dt

The same logic is applied for the OVERLAPS operator

Select A.id, A.trans_dt, trans_amt, B.pmt_meth
from trans1 A
left join scd1 B
  on A.id=B.id
 and (A.trans_dt, NULL) OVERLAPS (B.start_dt, B.end_dt)

Btw, Teradata supports Temporal Tables (in Teradata plus Standard SQL syntax) to implement SCD2 in a simplified way.

Teradata join with SCD type 2

2 Answers2