Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It might be a pretty basic thing for some of you but I'm trying to figure out the best approach for this.
I have 3 tables to combine (Sales Data, Territory, Sales Rep). In the example below, I am just showing the common fields that would link the tables together.
Sales Data
Inv_Date
Ship_zip
Territory
Ship_Zip
Territory Code
Sales Rep
Territory Code
Sales Rep Name
Hire Dt
Start Dt
End Dt
What I am trying to accomplish is to identify the Sales Rep for each line on the Sales Data and this will be based on the invoice date and the Start end date of the Sales Rep.
Any help? PLEASE...
Hi,
You could do it in two steps:
1. Create a table joining Sales_Data and Territory, on the field Shi_Zip, so that the new table has following columns:
Inv_Date, Territory Code, Ship_zip
2. Now do an extended interval match to SalesRep Table on Key TerritoryCode.
I have written a small article on interval match, will post the link later. Though the Reference manual should suffice for now.
After doing the interval match, if you want to avoid the synthetic keys, do use a left join to join the tables.
I believe you'll want to use an intervalmatch, but I've only used it once or twice, so would need to experiment to get the right syntaxt for your example. Just check the help text for intervalmatch, and see if it's what you're looking for.
Thanks John... Let me look into it as I have not used that command.
is there any one in the forum that has an experience using intervalmatch?
Hi,
You could do it in two steps:
1. Create a table joining Sales_Data and Territory, on the field Shi_Zip, so that the new table has following columns:
Inv_Date, Territory Code, Ship_zip
2. Now do an extended interval match to SalesRep Table on Key TerritoryCode.
I have written a small article on interval match, will post the link later. Though the Reference manual should suffice for now.
After doing the interval match, if you want to avoid the synthetic keys, do use a left join to join the tables.
Thanks Vidyut... It seems to work well. I will look more into the result to see if it covered what I needed. But everything makes perfect sense.