SQL select current transaction and reference most recent transaction

Question

could use some SQL help in Oracle 11G. I'm trying to create a result set that takes a current transaction, finds the most recent related transaction, shows the current price along with the previous price, and then calculates the difference.

Assume each item can only have one price for a given month. If there's no earlier data available, then display the current value.

Raw data would look something like:

+-------+----------+------------+------------+-------+
| Item  | Location | Department |  MonthEnd  | Price |
+-------+----------+------------+------------+-------+
| Truck | Illinois | BusinessA  | 4/30/2014  | 10000 |
| Truck | Illinois | BusinessA  | 6/30/2014  |  9500 |
| Truck | Illinois | BusinessA  | 10/31/2014 |  8000 |
+-------+----------+------------+------------+-------+

And the query result would look something like:

+-------+----------+------------+------------+-------+------------------+---------------+------------+
| Item  | Location | Department |  MonthEnd  | Price | PreviousMonthEnd | PreviousPrice | PriceDelta |
+-------+----------+------------+------------+-------+------------------+---------------+------------+
| Truck | Illinois | BusinessA  | 10/31/2014 |  8000 | 6/30/2014        |          9500 |      -1500 |
| Truck | Illinois | BusinessA  | 6/30/2014  |  9500 | 4/30/2014        |         10000 |       -500 |
| Truck | Illinois | BusinessA  | 4/30/2014  | 10000 | 4/30/2014        |         10000 |          0 |
+-------+----------+------------+------------+-------+------------------+---------------+------------+

Thanks in advance!

Answer 1

You should be able to use Lag analytical function to get that. The query will look like below.

SELECT Item,
       Location,
       Department,
       MonthEnd,
       Price,
       COALESCE(LAG (MonthEnd, 1) OVER (ORDER BY MonthEnd),  MonthEnd) PrevMonthEnd,
       COALESCE(LAG (Price, 1)    OVER (ORDER BY MonthEnd),  price)    PrevPrice ,
       (price - coalesce(LAG (Price, 1) OVER (ORDER BY MonthEnd), price)) PriceDelta
FROM   items
ORDER BY monthend desc

Here's a SQLFiddle testing this.