Question on how to find the price at the time of service

[Cdroyer]

Hello,

I am working on a database for SKUs that has price changes weekly. For one vendor, the prices are effective at different points of the week (so not every Sunday, but as the supplier changes). I would like to be able to see when a customer purchases the product what the price was for that vendor.


For example,

Customer buys part on 5/5/16, the supplier price as of 5/3/16 was $5. The price later changed on 5/14/16 to $5.25. I want to be able to run a query based off of my Customer Table that would give me the Supplier Price of $5 because that is what it cost when the customer purchased the part. I am running into some issues on how to frame the query to find the last previous date.

Any suggestions would be useful.

[June7]

If you don't save the price record ID into the purchase record, this can get rather complicated. Provide info on table structures and relationships, sample data would be helpful.

[Cdroyer]

Hi June7,

For simplicity these would be the tables and the columns that relate.

Purchase Table
SKU Number
Date of Service
Cost of Service

Supplier 1(Price can change whenever)
SKU Number
Date Price Changed
New Price


Supplier 2 (Price changes monthly)
SKU Number
Month Price Changed
Year Price Changed
New Price

[June7]

SKU is primary/foreign key? Why two tables for suppliers? One should serve regardless of frequency. Just adding complication.