Means of retrieval
I tested five methods of retrieving prices for all products
as they were 30 days ago from the PriceHistories table:
1) Using a
subquery to determine which record for each product is the correct one for 30
days ago. The subquery selects the maximum DateEntered that is less than 30
days for each product. In this article, I will refer to this method as subquery(max).
select p.ProductID,
p.Name,
p.Description,
prc.Price
from Products p
join PriceHistories prc
on p.ProductID = prc.ProductID
where DateEntered = (select max(DateEntered)
from PriceHistories
where ProductID =
p.ProductID
and DateEntered <
dateadd(dd, -30, getdate()))
2) Using a
subquery to determine which record for each product is the correct one for 30
days ago. The subquery selects the top 1 DateEntered that is less than 30 days,
ordering by DateEntered descending. In this article, I will refer to this
method as subquery(top).
select
p.ProductID,
p.Name,
p.Description,
prc.Price
from Products p
join PriceHistories prc
on p.ProductID = prc.ProductID
where DateEntered = (select top 1
DateEntered
from PriceHistories
where ProductID =
p.ProductID
and DateEntered <
dateadd(dd, -30, getdate())
order by
DateEntered desc)
3) Using cross
apply, as show by Grant Fritchey during his session on the optimizer in SQL
Pass summit 2009. The Products table is cross applied against the most recent
record of PriceHistories that is older than 30 days.
select p.ProductID,
p.Name,
p.Description,
prc.Price
from Products p
cross apply (select top (1) Price
from PriceHistories
where ProductID =
p.ProductID
and DateEntered <
dateadd(dd, -30, getdate())
order by
DateEntered desc)
as prc
4) Using string
manipulation. Concatenate the DateEntered with the price and use this
concatenation to determine the greatest DateEntered that is less than 30 days.
Return the substring of the concatenation that represents only the price.
select p.ProductID,
p.Name,
p.Description,
substring(max(convert(nvarchar(23), prc.DateEntered, 121) + convert (nvarchar(10), prc.Price)), 24, 8) Price
from Products p
join PriceHistories prc
on p.ProductID = prc.ProductID
where DateEntered <
dateadd(dd, -30, getdate())
group by p.ProductID,
p.Name,
p.Description
5) Using the rownumber
function in a common table expression to select the first record that older
than 30 days for each product, ordered by DateEntered.
;with
RankedPriceHistories
as (
select row_number() over(partition by ProductID
order by DateEntered
desc) Rank,
Price,
ProductID
from PriceHistories
where DateEntered <
dateadd(dd, -30, getdate()))
select p.ProductID,
p.Name,
p.Description,
prc.Price
from Products p
join RankedPriceHistories prc
on p.ProductID
= prc.ProductID
where prc.Rank = 1