SQL selects the current transaction and refers to the most recent transaction
may use some SQL help in Oracle 11G. I am trying to create a result set that takes the current transaction, finds the most recent associated transaction, shows the current price along with the previous price, and then calculates the difference.
Suppose each product can only have one price per month. If there is no previously available data, then display the current value.
The raw data looks something like this:
+-------+----------+------------+------------+-------+
| 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 will look something like this:
+-------+----------+------------+------------+-------+------------------+---------------+------------+
| 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!
source to share
You can use the Lag analytic function to get this. The request 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 checking this out.
source to share
Use the analytic function to generate results row_number
and Left join
. Try it.
WITH cte
AS (SELECT *,Row_number()OVER (ORDER BY MonthEnd DESC) rn
FROM yourtable)
SELECT a.Item,
a.Location,
a.Department,
a.MonthEnd,
a.Price,
COALESCE(( a.Price - b.Price ), a.price)
FROM cte a
LEFT JOIN cte b
ON a.rn = b.rn - 1
Note. According to your requirement, you can add Partition by
to the Over
offer
source to share
Another solution using Oracle Analytical functions (using Windowing clause). You can find a modified version of @ Sathya's SQLFiddle here
The request looks like this:
SELECT Item,
Location,
Department,
MonthEnd,
Price,
MIN(monthend) OVER (PARTITION BY item,location,department ORDER BY item,location,department,monthend ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) PrevMonthEnd,
NVL(SUM(price) OVER (PARTITION BY item,location,department ORDER BY item,location,department,monthend ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),price) PrevPrice ,
(price - NVL(SUM(price) OVER (PARTITION BY item,location,department ORDER BY item,location,department,monthend ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),price)) PriceDelta
FROM items
ORDER BY monthend DESC
source to share