I have a View in my database set up to display data for parts that my company uses for projects.
One of the columns in the View shows what our recorded price for the part is. However, as time goes on, and prices change, there will be many such entries for part prices.
Is there a way within a View setup, to set it so that the value retrieved for a column is that last value in that table?
Or is this a situation where I'd need to include the date of the price log as part of the view data, and then do a
TOP 1 ... ORDER BY DESC type query against my view?
For example right now if I query my View for specifically the part with
Part_Number = '0010.05.20.20' I will get the following Table result:
Part_Number Supplier Manufacturer Price Discount Currency 0010.05.20.20 A2A Systems Vario 55.97 1.000 CAD 0010.05.20.20 A2A Systems Vario 50.25 1.000 USD
I know that the
50.25 price value is the most recent one, because it is the one I just inserted into the database for testing purposes. However, there is no way to know just from doing a
SELECT * FROM PartsView query which price is the most current one.
This is the code for creating my View, to clarify, I am wondering if there is a way to modify this so that the
Price column only shows the most recent entry for any criteria it is looking up. If that is not possible, is my only discourse to add a 'Log Date' Column for when the price was logged?
CREATE VIEW PartsView AS SELECT p.part_number AS [Part_Number], s.supplier_name AS [Supplier], m.manufacturer_name AS [Manufacturer], pl.cost_per_unit AS [Price], pl.discount_percentage AS [Discount], cu.currency_type AS [Currency] FROM dbo.parts AS p INNER JOIN dbo.suppliers AS s ON p.supplier_id = s.supplier_id INNER JOIN dbo.price_logs AS pl ON p.part_id = pl.part_id INNER JOIN dbo.currencies AS cu ON pl.currency_id = cu.currency_id GO