Joining two slowly changing tables

[#1]

Hello all -

I have two tables that are slowly chaging, but with a common OK.   I need to join them.  I've seen the IntervalMatch examples, but I'm not sure how to manage it with two slowly changing tables instead of one.

Example:

TableA:

SystemID, Status, Start, End

1, Active, 1/1/2011, 5/31/2011

1, Closed, 6/1/2011, 10/31/2011

1, Active, 11/1/2011, 2/31/2013

2, Active, 1/1/2012, 12/31/2012

2, Closed, 1/1/2013. 2/31/2013

TableB:

SystemID, Territory, Start, End

1, A, 1/1/2011, 12/31/2011

1, B, 1/1/2012, 2/31/2013

2, C, 1/1/2012, 5/31/2012

2, B, 6/1/2012, 2/31/2013

The Resulting table should be:

Final:

System ID, Status, Territory, Start, End

1, Active, A, 1/1/2011, 5/31/2011

1, Closed, A, 6/1/2011, 10/31/2011

1, Active, A, 11/1/2011, 12/31/2011

1, Active, B, 1/1/2012, 12/31/2011

2, Active,C, 1/1/2012, 5/31/2012

2, Active,B, /61/2012, 12/31/2012

2, Closed,B, 1/1/2013. 2/31/2013

Thanks!

[hic · Accepted solution]

I would use a While loop to create the dates in the interval. But it can be done with intervalmatch also.

The solution contains three synthetic keys, but in this case they are correct and - in fact the most efficient solution. If you want you can probably change the script to manually created composite keys instead.

/HIC

TableA:

Load SystemID as SystemIDA,

          Status,

          Date#(Start) as StartA,

          Date#(End) as EndA

          inline

[SystemID, Status, Start, End

1, Active, 1/1/2011, 5/31/2011

1, Closed, 6/1/2011, 10/31/2011

1, Active, 11/1/2011, 2/31/2013

2, Active, 1/1/2012, 12/31/2012

2, Closed, 1/1/2013, 2/31/2013];

TableB:

Load SystemID as SystemIDB,

          Territory,

          Date#(Start) as StartB,

          Date#(End) as EndB

          inline

[SystemID, Territory, Start, End

1, A, 1/1/2011, 12/31/2011

1, B, 1/1/2012, 2/31/2013

2, C, 1/1/2012, 5/31/2012

2, B, 6/1/2012, 2/31/2013];

IntervalMatchA:

Load SystemIDA as SystemID, StartA, EndA,

          Date;

Load *,

          Date(StartA + IterNo() - 1) as Date

          Resident TableA

          While IterNo() <= EndA - StartA + 1;

IntervalMatchB:

Load SystemIDB as SystemID, StartB, EndB,

          Date;

Load *,

          Date(StartB + IterNo() - 1) as Date

          Resident TableB

          While IterNo() <= EndB - StartB + 1;

[#3]

Thanks! This get's me what need, I can then load a table from them and drop the old ones.  I searched around, but all of the examples were fact to slowly changing dimension.

Strangely, I have a couple of extra days in the Date field that aren't in the source - 3/1/2013-3/3/2013, but I'll figure that out.