0

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
  • 2
    If your view returns only 1 row, you can use TOP 1. If not, use some window function (for example, FIRST_VALUE) with proper OVER partitioning and ordering. It will be good if you show the original data and the desired result. – Akina Jul 30 '18 at 12:29
  • Could you update your question and include a Minimal, Complete, and Verifiable example? – Scott Hodgin Jul 30 '18 at 12:33
  • @ScottHodgin I added a bit more to the question – Skitzafreak Jul 30 '18 at 12:47
  • @Skitzafreak - There would have to be a DATE column on one of these tables that would document the date of the price change. Do you have such a column on the price_logs table? Also, what version of SQL Server are you using? 2016, 2017, etc.? – Scott Hodgin Jul 30 '18 at 12:58
  • @ScottHodgin Yes I have a DATE column in my price_logs table that I can add to the View. I am using SQL Server 2012 since it's what our company has a license for. – Skitzafreak Jul 30 '18 at 13:06
1

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  |
  • Is there a way for me to put that AND pl.TransactionDate =( in as part of the view? Or can that not be done? – Skitzafreak Jul 30 '18 at 13:19
  • Yes, you should be able to include that as part of your view - try it out. Since you haven't posted any sample data in your question, we're all kind of guessing about the source data. – Scott Hodgin Jul 30 '18 at 13:21
  • thank you :) I was getting an error when I tried putting it in, which is why I was asked if it was possible. However the error was with another part of the code that accidentally got modified adding that snippet in. Thank you for the help :) – Skitzafreak Jul 30 '18 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.