TSQL Query to fetch row details from a table when the value of a column last changed

I have two tables.

Table 1

   Item
    1
    2

Table 2

   Item   Date    Amount
    1     12/31     30
    1     12/30     30
    1     12/29     20
    2     12/31     100
    2     12/30     90
    2     12/29     90

Now my result should have

   Item   Date    Amount
    1    12/29     20
    2    12/30     90 
    3    12/31     12

Basically, i am trying to fi开发者_运维百科nd out the date when the price changed recently. In turn, i will use this information to calculate the no of days the item is at the current price.

Thanks

;WITH cte
     AS (SELECT *,
                Row_number() OVER (PARTITION BY Item ORDER BY Date) -
                Row_number() OVER (PARTITION BY Amount, Amount ORDER BY Date) AS
                grp
         FROM   table2)
SELECT Item,
       MAX(Amount) AS Amount,
       MIN(Date)   AS startrange,
       MAX(Date)   AS endrange,
       1+DATEDIFF(DAY,MIN(Date),MAX(Date)) AS numdays
FROM   cte
GROUP  BY grp,
          Item  

Returns the following for your test data

Item        Amount      startrange endrange   numdays
----------- ----------- ---------- ---------- -----------
1           20          2010-12-29 2010-12-29 1
1           30          2010-12-30 2010-12-31 2
2           90          2010-12-29 2010-12-30 2
2           100         2010-12-31 2010-12-31 1

Try this:

SELECT Item, Date, Amount
FROM
(
    SELECT
        T2.Item,
        T2.Date,
        T2.Amount,
        ROW_NUMBER() OVER (PARTITION BY T2.Item ORDER BY T2.Date DESC) rn
    FROM table2 T2
    JOIN
    (
        SELECT Item, Amount
        FROM
        (
            SELECT
                Item,
                Amount,
                ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC) rn
            FROM table2
        ) T1
        WHERE rn = 1
    ) T3
    ON T2.Item = T3.Item AND T2.Amount <> T3.Amount
) T4
WHERE rn = 1

Result for your example data:

Item  Date        Amount
1     2010-12-29  20    
2     2010-12-30  90    

Explanation

The subquery T3 finds the most recent price for each item by using ROW_NUMBER. This is then joined back to the original table and rows where the price of an item is equal to the most recent price are removed. The most recent price of the remaining data is then found for each item, again using the ROW_NUMBER technique. This is the second most recent price.