history table join

[harryhome]

two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)

table one

effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven

table two

effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W



Expected output

effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W



Is this possible in transformer or any other stage in DS?

[anbu]

Can you explain in words what are you trying to do?

[kris007]

I am not sure how you got the output records in your examples with the data you have provided? What are the conditions you are trying to meet?

[harryhome]

Hi,
Actual these are the SCD 2 type source tables. means for ID = 1, we have all previous changes history with effective and expiration date in both the table.
And output is join of these tables.

Product X was priced 100rs for year 2000 to 2006
product X was priced 200rs for year 2006 to 2009

a product X belongs to family A for year 2000 to 2004
a product X belongs to family B for year 2004 to 2007
a product X belongs to family C for yesr 2007 to 2009

so I have

Product X was priced 100rs and belongs to family A for year 2000 to 2004
Product X was priced 100rs and belongs to family B for year 2004 to 2006
Product X was priced 200rs and belongs to family B for year 2006 to 2007
Product X was priced 100rs and belongs to family C for year 2007 to 2009


So here is the join for two SCD2 type table.

[Sainath.Srinivasan]

Assuming they reside on the same database (and preferably same schema), you can do the following

Code: Select all

SELECT a.ID, a.Name, b.Family, a.Eff_From_Dt, a.Eff_To_Dt, b.Eff_From_Dt, b.Eff_To_Dt
FROM table1 a, table2 b
WHERE a.ID = b.ID
AND 
(
(a.Eff_From_Date Between b.Eff_From_Date and b.Eff_To_Dt) 
OR 
(a.Eff_To_Date Between b.Eff_From_Date and b.Eff_To_Dt) 
OR
(b.Eff_From_Date Between a.Eff_From_Date and a.Eff_To_Dt) 
OR
(b.Eff_To_Date Between a.Eff_From_Date and a.Eff_To_Dt) 
)

This can then be followed by a transformer to derive your output by taking the smaller window.