Efficiently query for the latest version of a record using SQL

Viewed 64

I need to query a table for the latest version of a record for all available dates (end of day time-series). The example below illustrates what I am trying to achieve.

My question is whether the table's design (primary key, etc.) and the LEFT OUTER JOIN query is accomplishing this goal in the most efficient manner.

CREATE TABLE [PriceHistory]
(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [ItemIdentifier] [varchar](10) NOT NULL,
    [EffectiveDate] [date] NOT NULL,
    [Price] [decimal](12, 2) NOT NULL,

    CONSTRAINT [PK_PriceHistory] 
       PRIMARY KEY CLUSTERED ([ItemIdentifier] ASC, [RowID] DESC, [EffectiveDate] ASC)
)

INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-15',5.50)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-16',5.75)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-16',6.25)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-17',6.05)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-18',6.85)
GO

SELECT 
    L.EffectiveDate, L.Price
FROM 
    [PriceHistory] L
LEFT OUTER JOIN 
    [PriceHistory] R ON L.ItemIdentifier = R.ItemIdentifier 
                     AND L.EffectiveDate = R.EffectiveDate 
                     AND L.RowID < R.RowID
WHERE 
    L.ItemIdentifier = 'ABC' and R.EffectiveDate is NULL
ORDER BY 
    L.EffectiveDate

Follow up: Table can contain thousands of ItemIdentifiers each with dacades worth of price data. Historical version of data needs to be preserved for audit reasons. Say I query the table and use the data in a report. I store @MRID = Max(RowID) at the time the report was generated. Now if the price for 'ABC' on '2016-03-16' is corrected at some later date, I can modify the query using @MRID and replicate the report that I ran earlier.

3 Answers
Related