I need help with creating a longitudinal table. Table 1 contains customer_ids with one record each. Table 2 contains multiple rows for customer_ids by month (201501,201502....201512). In this example I'm getting three months worth of data by doing three joins.
Is there a smarter more efficient way in Teradata to do this. For instance, if I needed twelve months worth of data for every customer_id, instead of doing 12 joins, can it be done in better way ? THANKS!
create table longitudinal_data as
(
select
a.*,
b.x ,b.y ,b.z,
c.x as x2,c.y as y2,c.z as z2,
d.x as x3,d.y as y3,d.z as z3
from table1 a
ier join table2 b on a.cust_no = b.cust_no and a.min_txn_date - 1 = b.upd_seq
ier join table2 c on a.cust_no = c.cust_no and a.min_txn_date - 2 = c.upd_seq
ier join table2 d on a.cust_no = d.cust_no and a.min_txn_date - 3 = d.upd_seq
)with data;