You might want to union the data instead of joining it - a join would append additional columns to the data set rather than adding the new price changes as just additional records. This does depend on what you are trying to visualize between the two tables. I'm also not entirely clear whether the columns correspond to each other exactly (does the "Cost" in both tables mean the same thing?), but it seems likely that this might be what you want to do. The core idea is combining both tables along the date, item cost, and cost; rather than having unique date fields that represent "Change Date" and "Date of Sale" both are combined to the same column in a disjointed table:
Event Type Sales Rep Date Item Code Buyer Cost Price Change N/A 2017/01/01 MGA442 N/A 0.55 Sale B 2017/12/12 MGA442 XX 1.3 Price Change N/A 2018/01/01 MGA442 N/A 0.95 Sale A 2018/01/12 MGA442 ZZ 1.3 Price Change N/A 2018/02/02 MGA442 N/A 1.3 Sale C 2018/02/02 MGA442 VV 1.3
Because the tables do not have the same columns originally, custom SQL can help structure this format across the two tables. For Excel, you will need to connect to the file with the Excel legacy connector to be able to connect to data via custom SQL:
"Sale" AS [Event Type],
[Sales].[Sales Rep] AS [Sales Rep],
[Sales].[Date of Sale] AS [Date],
[Sales].[Item Code] AS [Item Code],
[Sales].[Buyer] AS [Buyer],
[Sales].[Cost] AS [Cost]
"Price Change" AS [Event Type],
"N/A" AS [Sales Rep],
[Price Change].[Change Date] AS [Date],
[Price Change].[Item Code] AS [Item Code],
"N/A" AS [Buyer],
[Price Change].[Cost] AS [Cost]
FROM [Price Change]
You may need to adjust depending on how Excel reads the table names, but this type of query will get you an output table as described above. If you are thinking of a different structure of the data , let me know and we can see what we can recommend.
I had a similar issue that I solved in SQL Server and I imagine could be done in CustomSQL; maybe this will work for you or at least get you close. I had a series of milestones, but wanted to fill in the gap between the milestones with dates so I could answer the question, "What milestone was this order at on X date?". There are two components you need: Next Date and a JOIN Clause.
For the Next Date, you can use the LEAD function in SQL on the bottom table to show when the adjustments are active:
LEAD([Change Date],1) OVER (PARTITION BY [Item Code] ORDER BY [Change Date] asc) as [Next Change Date].
This will give you the next change date and a null for the current for each Item Code.
When you join, you just plot out the scenarios and say
ON a.[Date of Sale]>=b.[Change Date] AND a.[Date of Sale] <= IIF(b.[Next Change Date] IS NULL, cast(getdate() as date), b.[Next Change Date]).
This should basically say, if you sales date is greater than the change date, but less than the next change date, use that adjustment. If there is not a new change date, the current change date, then you would use today's date as the upper threshold. Now you just are asking whether a date is between two dates on a single record.