03-31-2015 10:47 AM
I'm trying to merge 2 data sets. One has many rows with a date and the other has 3 rows and a data range with the aplicable value that i need to be picked up.
I need to show the correct value for each row in a by picking up the correct value within the date range specified.
I have tried the following -
create table work.EDW_ALL as
from EDW_PLUS_CPC a
left join DCA_PERCENT b
and PURCHASE_DATE >= CPA_START_DT
AND PURCHASE_DATE <= CPA_EXP_DT
This gives me all the 225 rows I need but only gives me the merged value when the date falls into the first range on the b dataset,
merge EDW_PLUS_CPC (in=a)
by DEALER_CODE CPC DLR_IP;
if (PURCHASE_DATE >= CPA_START_DT
AND PURCHASE_DATE <= CPA_EXP_DT);
if a then
This gives me just 10 rows where the date falls into the first range on the b dataset.
It would appear that the first row is being picked up rather than picking up the correct value for each date/row.
Any thoughts would be gratefully appreciated.
03-31-2015 12:51 PM
Do you get the desired results using a where clause in the second example? Depending on where the data comes from you can do this:
dca_percent(in=b where=(PURCHASE_DATE >= CPA_START_DT AND PURCHASE_DATE <= CPA_EXP_DT));