Difference in the latest and second-latest price

I have two tables as follows:

Commodity
---------
Id        Name
1         Test 
2         SecondTest

CommodityPrice
--------------
Id      CommodityID    Price     EffectiveDate
0            1         0.66      05/01/2011
1            1         1.00      06/01/2011
2            1         1.50      07/01/2011
3            2         3.00      05/01/2011         
4            2         5.00      06/01/2011
5            2         10.00     07/01/2011

I'm attempting to write a query with the following output:

Result
-------
Name          PriceChange
Test          0.50
SecondTest    5.00

I've got this query which gets the most recent price, but it's not dealing with price differences yet.

SELECT c1.Name, cp1.Price
FROM Commodities c1
INNER JOIN CommodityPrices cp1
ON c1.Id = cp1.CommodityId
WHERE EffectiveDate = 
(SELECT MAX(cp2.EffectiveDate)
FROM Commo开发者_如何学CdityPrices cp2
WHERE c1.Id = cp2.CommodityId);

I would like to find the price difference between the two most recent prices for a commodity. Note, that this should ignore prices earlier than the most recent two.

This assumes SQL 2005 or later. Just use ROW NUMBER to assign numbers to the rows and then JOIN ON a.id = b.id AND b.rn = a.rn - 1.

WITH
     cte 
     AS (SELECT c.id, 
                c.name, 
                cp.price, 
                cp.effectivedate, 
                Row_number() OVER (PARTITION BY c.id ORDER BY cp.effectivedate 
                DESC) 
                rn 
         FROM   commodity c 
                INNER JOIN commodityprice cp 
                  ON c.id = cp.commodityid) 
SELECT a.name, 
       b.price - a.price pricechange 
FROM   cte a 
       INNER JOIN cte b 
         ON a.id = b.id 
            AND b.rn = a.rn - 1 
WHERE b.rn = 1

Here's the sample data that I used

WITH Commodity as 
(           SELECT 1  as ID , 'Test' as name
 UNION ALL  SELECT 2, 'SecondTest'),
CommodityPrice as 
(   SELECT 1      as Id      , 1         as CommodityID , 1.00  as Price,    '06/01/2011' as EffectiveDate
    UNION ALL SELECT  2            ,1,         1.50      ,'07/01/2011'
    UNION ALL SELECT  4            ,2         ,5.00      ,'06/01/2011'
    UNION ALL SELECT  5            ,2         ,10.00     ,'07/01/2011'
UNION ALL SELECT  0            ,1         ,0.66      ,'05/01/2011'
UNION ALL SELECT  3            ,2         ,3.00      ,'05/01/2011'
 ),

Which produced this output

name       pricechange
---------- ---------------------------------------
Test       0.50
SecondTest 5.00

(2 row(s) affected)

Note: you could also drop AND b.rn = a.rn - 1 from the JOIN and add AND a.rn = 2 to the WHERE

Without ranking functions:

Test data:

declare @CommodityPrice table(id int identity, CommodityID int, Price money, EffectiveDate date)

insert @CommodityPrice(CommodityID, Price, EffectiveDate) 
    values (1, 0.66, '05/01/2011'), (1, 1, '06/01/2011'), (1, 1.5, '07/01/2011'),
        (2, 3, '05/01/2011'), (2, 5, '06/01/2011'), (2, 10, '07/01/2011')

Query:

select a.CommodityID, c2.Price - c1.Price
from
(
    select c1.CommodityID, MAX(c1.EffectiveDate) [m1], MAX(c2.EffectiveDate) [m2]
    from @CommodityPrice c1
    join @CommodityPrice c2 on c2.CommodityID = c1.CommodityID
        and c2.EffectiveDate > c1.EffectiveDate
        and not exists (
            select 1
            from @CommodityPrice c3
            where c3.EffectiveDate > c1.EffectiveDate and c3.EffectiveDate < c2.EffectiveDate
        )
    group by c1.CommodityID
)a
join @CommodityPrice c1 on c1.CommodityID = a.CommodityID and c1.EffectiveDate = a.m1
join @CommodityPrice c2 on c2.CommodityID = a.CommodityID and c2.EffectiveDate = a.m2