I am building a Point of sales software with Sql Server 2008-R2. I am now confused about how to handle multiple items with prices change.
Let us say price of Pepsi is $2.0 on 10-09-2019 but today it's price is change to $2.50. and may be in coming days it's price will again change and will come back on $2.00.
So at the end of the month when i need monthly report of items. Then how i will deal with this type of situation. Either i will check it's current price or the price before 10 days or average of both the prices ?
Should i need a separate table to keep track of price changes of all items and query each item 's price for the specific date for reporting ?
What would be best approach according to experts to deal with this type of situation ?
Any guidance will be really appreciable.