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

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |