Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do what I was trying to do.

Instead of filtering the table during import, I now want to create a view on the table that returns only records where the price has changed.

Here's the original scenario rephrased to suite this question:

We have a table of historical prices for items. The table contains rows where the same price is recorded for multiple dates. I want to create a view on this data which only shows price changes over time, so if a price changes from A to B I want to see it, but if it "changes" from B to B then I don't want to see it.

Example: if the price yesterday was $1, and the price today is $1, and there were no other price changes, then the price today can be inferred from the price yesterday so I only need the record from yesterday.

Example (http://sqlfiddle.com/#!3/c95ff/1):

Table data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-24T00:24:00  1234     1     1.00 -- redundant, implied by record 1
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-27T00:27:00  1234     1     2.00 -- redundant, implied by record 4
2013-04-28T00:28:00  1234     1     1.00 -- not redundant, price changed back to 1.00

Expected view data:

Effective            Product  Kind  Price
2013-04-23T00:23:00  1234     1     1.00
2013-04-25T00:25:00  1234     1     1.50
2013-04-26T00:26:00  1234     1     2.00
2013-04-28T00:28:00  1234     1     1.00

My initial attempt used ROW_NUMBER:

SELECT
    Effective,
    Product,
    Kind,
    Price
FROM
(
    SELECT
        History.*,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                Product,
                Kind,
                Price
            ORDER BY
                Effective ASC
        ) AS RowNumber
    FROM History
) H
WHERE RowNumber = 1
ORDER BY Effective

Which returned:

Effective               Product  Kind  Price
2013-04-23T00:23:00     1234     1     1.00
                                             -- not 2013-04-24, good
2013-04-25T00:25:00     1234     1     1.50
2013-04-26T00:26:00     1234     1     2.00
                                             -- not 2013-04-27, good
                                             -- not 2013-04-28, bad

I tried searching for a similar question/answer but it's hard to work out how to phrase the search, an example is worth a lot of words.

Any suggestions appreciated. Thanks

share|improve this question
    
You join the table with itself (subselect\CTE might be better) and find all the rows, that had a differance from the oldest row, before the date of the current row. –  AK_ Jul 30 '13 at 14:30
1  
possible duplicate of Query (duration) different between data value change –  Jon Seigel Jul 30 '13 at 16:07
    
@JonSeigel, thanks for pointing out that question. It was close enough that I could modify your answer to do what I needed. I posted the modified answer here with a credit to your answer on the other question. –  WileCau Jul 31 '13 at 5:36

5 Answers 5

up vote 7 down vote accepted

SQLfiddle

SELECT H.*
FROM History AS H
OUTER APPLY
(
    SELECT TOP (1)
        H2.Price
    FROM History AS H2
    WHERE
        H2.Product = H.Product
        AND H2.Kind = H.Kind
        AND H2.Effective < H.Effective
    ORDER BY
        H2.Effective DESC
) AS X
WHERE
    NOT EXISTS (SELECT X.Price INTERSECT SELECT H.Price);

Execution plan

This is a good execution plan for the small number of rows given in the question. For a larger table, the ideal index for this query is:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective DESC)
INCLUDE (Price);

Execution plan 2

That index is essentially the clustered index keys in a more helpful order. Depending on how the table is used for other queries, it might be better to replace the clustered index instead of creating this new index.

Do not use batch estimated cost percentages to compare different queries. This is not a valid comparison in general. The costs are always optimizer estimates, and not intended to be used this way. Check actual performance metrics (elapsed time, I/Os, CPU usage, memory usage) by all means, but do not put your faith in the percentages.

Whether this query or the one based on ROW_NUMBER is more efficient depends on the distribution of the data, and other factors. Each has their advantages and disadvantages. This query has advantages when there are many rows for each (product, kind) combination.

share|improve this answer
    
Thanks Paul. I ran your query and the one I posted in a batch to compare them. Using the original table (7 rows) the result was 16%/84% (yours/mine). I tried different size tables, and by 100k rows it was 99%/1%. At 100k rows your plan included a 97% Sort (Top N Sort) (SQLFiddle creates 100k random rows if you want to check). Is that expected? At 10k rows SSMS suggested an index on Product/Kind/Effective, but adding only brought the result to 77%/23% for 100k. –  WileCau Aug 1 '13 at 5:31
    
@WileCau Extended my answer to address your comments. –  Paul White Aug 2 '13 at 1:50
SELECT  *
FROM   (SELECT TOP 1 *
        FROM    History
        ORDER BY Effective ASC) AS f

UNION ALL

SELECT  *
FROM    History AS a
WHERE   a.Price <> (SELECT TOP 1 Price
                    FROM    History AS b
                    WHERE   b.Effective < a.Effective 
                    ORDER BY Effective DESC) 

ORDER BY Effective ASC ;

Test at SQL-Fiddle

Execution plan

share|improve this answer
2  
I think it needs a UNION ALL to include the first row in the table (which will not be returned, due to the compare to NULL), but it should work as the OP wants. –  ypercube Jul 30 '13 at 15:10
    
@ypercube good point.... –  AK_ Jul 30 '13 at 15:12
    
Thanks, that did the trick and is surprisingly quick. Can you please edit your answer to include the UNION ALL that @ypercube suggested, then I'll accept this answer. Adding the union will make it easier for future generations who might find their way here :) –  WileCau Jul 31 '13 at 3:32
    
The results in the SQL-Fiddle don't include the value for 2013-04-27 when the price changes to $2 for Kind 1. The top part of the union needs to consider Product and Kind. I modified the SQL-Fiddle. –  WileCau Jul 31 '13 at 5:56

Thanks to @JonSeigel for directing me to Query (duration) different between data value change. I'm posting a modified version of his answer here because my problem was slightly different (needed PARTITION BY in the ROW_NUMBER) so maybe someone will find the variant helpful. If you find this answer helpful please vote up the Jon's answer because I wouldn't have got to this without it.

In my fairly basic timing tests on a table containing ~3M records this solution is about 3x faster than @AK_'s answer.

This is the query:

WITH a AS
(
  SELECT
    History.*,
    ROW_NUMBER() OVER (PARTITION BY Product, Kind ORDER BY Effective ASC) AS RN
  FROM History
)
SELECT
  a1.Effective,
  a1.Product,
  a1.Kind,
  a1.Price
FROM a a1
LEFT OUTER JOIN a a2
  ON a2.RN = a1.RN - 1
    AND a2.Product = a1.Product
    AND a2.Kind = a1.Kind
WHERE
  (a1.Price <> a2.Price)
  OR (a2.RN IS NULL)

Execution plan

For a larger number of rows:

Execution plan 2

The sorts can be eliminated using the index:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective)
INCLUDE (Price);

Execution plan 3

share|improve this answer

What is needed is a LEAD/LAG solution implemented in 2008. Unfortunately it didn't become available until 2012. I found a couple really good implementations over on SQLAuthority. A comparison can be made between the LEAD and current value and row returned where they are different.

share|improve this answer
    
Thanks for the link, I hadn't heard of LEAD/LAG. Unfortunately we're stuck with 2008 for now so I'll definitely go through those solutions to see if/where we can use them. –  WileCau Jul 31 '13 at 3:38
1  
Can you expand your answer to show how LEAD/LAG can be used to solve the problem? Future visitors to the site may well be using SQL Server 2012 and will appreciate it. –  Paul White Jul 31 '13 at 9:56

For Sql Server 2012 using Lag()

;with h as(
SELECT  Effective,
    Product,
    Kind,
    Price, 
    lag (price, 1, 0) OVER (PARTITION BY product,kind order by Effective) as prevprice
FROM History )
select Effective, Product, Kind, Price
 from h where (prevprice <> price or prevprice = 0.00)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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