This question already has answers here:
Join two tables date from first in range of dates from second (3 answers)
Closed 4 years ago.
I am writing a report to determine the Average Selling Price of Items compared to the list price for those items historically.
I can get all the actual sales prices from the Value Entry Table.
The system 'List Price' is in the Sales Price table.
The tricky part is the prices change so the list price for a given item is different depending on the date the item was sold. So to accurately report this going back I need to identify the list price as it was on the date sold.
The Sales Price table fortunately has a Start Date and End Date for the price.
So I'm trying to figure how to join the Sales Price Table onto the Value Entry Table where the Date the item was sold falls between the Start Date and End Date for a line in the Sales Price Table
EX
Item No List Price Start Date End Date
1001 $1.00 01-01-18 02-28-18
1001 $1.25 03-01-18 05-31-18
1001 $1.50 06-01-18 08-31-18
Item 1001 Sold on 01-21-18 how do I get the $1.00 price to join on correctly?
2条答案
按热度按时间uidvcgyl1#
An outer join should do:
ovfsdjhp2#
You can use below query: