Sql-server – Set up View Column to be last Value

sql serverview

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?

EDIT:

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

Best Answer

Here is an example of how you can pick the latest price for a part.

declare @Parts table (PartID int, PartName varchar(50))
declare @PartsLog table (PartID int, TransactionDate Datetime, Price decimal(11,2))

insert into @Parts(PartID, PartName) values
    (1,'Hammer'),
    (2,'Drill')
insert into @PartsLog(PartID,TransactionDate,Price) values
    (1,'2018-01-01',100.00),
    (1,'2018-07-01',200.00),
    (2,'2018-01-01',50.00),
    (2,'2018-03-01',75.00)

SELECT p.PartID
    ,p.PartName
    ,pl.price
FROM @Parts p
JOIN @PartsLog pl
    ON pl.PartID = p.PartID
        AND pl.TransactionDate = (
            SELECT max(TransactionDate)
            FROM @PartsLog
            WHERE PartID = pl.PartID
            )

| PartID | PartName | price  |
|--------|----------|--------|
| 1      | Hammer   | 200.00 |
| 2      | Drill    | 75.00  |
Related Question