intervalmatch

[#1]

hi all,

in the following 2 tables, how can i get discount into Trans table? thanks a lot.

i tried to use intervalmatch but got stucked. there are tonned of data, so i didn't want to loop the start date til end date for each AddrNum, discount and then join back to Trans table.

any suggestion is very appreciated. thanks!

Trans:
load * inline [
AddrNum, price, date
A1001, 200, 1/20/2009
A1002, 150, 3/15/2009
A1002, 100, 2/5/2009
A1003, 150, 3/4/2009
];

SmallTrans:
load * inline [
AddrNum, discount, datestart, dateend
A1001, 0.05, 1/1/2009, 2/15/2009
A1001, 0.1, 2/16/2009, 3/31/2009
A1002, 0.05, 3/1/2009, 3/31/2009
A1002, 0.06, 4/1/2009, 4/15/2009
A1002, 0.07, 5/16/2009, 5/28/2009
A1003, 0.2, 1/1/2009, 1/15/2009
A1003, 0.3, 1/16/2009, 1/31/2009
A1003, 0.4, 2/1/2009, 2/28/2009
A1003, 0.5, 3/1/009, 3/20/2009
];

[hector]

Hi, I made this test model, check if it works for you.

C ya!

PD. maybe is the worst ever solution, buy it works , and if anybody post a better solution I will learn it

[#3]

here you go, using intervalmatch