Retrieving Data from an Audit Table

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