CODEWITHSUNDEEP
sqloracle-database
Joshua Tinashe
Joshua Tinashe

Reputation: 83

Oracle Query: Picking the last change in data in a column

I have a table that has the following columns:

Product_ID Pricing_Date Current_Price Last_Pricing_Date

I am trying to write a query that shows the old price (Last_Price) of the product before it was changed to the current price so that my results look like the below

PRODUCT_ID  PRICE_DATE  PRODUCT_PRICE LAST_PRICE
BlueLotion  24/08/2018   £10.00        £7.50
BlueLotion  23/08/2018   £10.00        £7.50
BlueLotion  22/08/2018   £10.00        £7.50
BlueLotion  21/08/2018   £7.50         £6.50 
BlueLotion  20/08/2018   £7.50         £6.50 
BlueLotion  19/08/2018   £7.50         £6.50
BlueLotion  17/08/2018   £6.50         £7.50
BlueLotion  16/08/2018   £6.50         £7.50
BlueLotion  13/08/2018   £6.50         £7.50
BlueLotion  12/08/2018   £7.50          NULL
BlueLotion  11/08/2018   £7.50          NULL
BlueLotion  10/08/2018   £7.50          NULL

Effectively picking what the value of the data was before the change. Some resources to test - you can quickly create a table using script:

create table COMP_RESULTS (product_id varchar2(20), price_date date, product_price number);

insert into comp_results values ('BlueLotion','24 AUG 2018','10');
insert into comp_results values ('BlueLotion','23 AUG 2018','10');
insert into comp_results values ('BlueLotion','22 AUG 2018','10');
insert into comp_results values ('BlueLotion','21 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','20 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','19 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','18 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','17 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','16 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','15 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','14 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','13 AUG 2018','7.5');

I'm sorry everyone, can I add one other complication. I need to also bring a column into the results table that shows the Last_Date_With_Prev_Price The final results set would therefore be

PRODUCT_ID  PRICE_DATE  PRICE  LAST_PRICE  DATE_WITH_PREV_RATE
BlueLotion  24/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  23/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  22/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  21/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  20/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  19/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  17/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  16/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  13/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  12/08/2018  £7.50   NULL        NULL
BlueLotion  11/08/2018  £7.50   NULL        NULL
BlueLotion  10/08/2018  £7.50   NULL        NULL

Upvotes: 3

Views: 1210

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

This is tricky because your prices go up and down. You can use lag(ignore nulls). First, find when the price changes. Then get the previous price. So:

select cr.*,
       (case when prev_current_price <> current_price
             then prev_current_price  -- use the previous price when it changes
             else  -- lag to the previous change
                  lag( (case when prev_current_price <> current_price then prev_current_price
                        end) ignore nulls
                      ) over (partition by cr.product_id
                              order by cr.price_date
                             )
        end) as prev_price
from (select cr.*,
             lag(cr.current_price) over (partition by cr.product_id order by cr.price_date) as prev_current_price
      from comp_results cr
     ) cr;

Upvotes: 2

MT0
MT0

Reputation: 168671

You can use a combination of the analytic functions LEAD and LAG (with the IGNORE NULLS option) like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table COMP_RESULTS (product_id varchar2(20), price_date date, product_price number);

insert into comp_results values ('BlueLotion',DATE '2018-08-24','10');
insert into comp_results values ('BlueLotion',DATE '2018-08-23','10');
insert into comp_results values ('BlueLotion',DATE '2018-08-22','10');
insert into comp_results values ('BlueLotion',DATE '2018-08-21','7.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-20','7.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-19','7.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-18','6.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-17','6.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-16','6.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-15','7.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-14','7.5');
insert into comp_results values ('BlueLotion',DATE '2018-08-13','7.5');

Query 1:

SELECT product_id,
       price_date,
       product_price,
       LAG( prev_price ) IGNORE NULLS OVER ( PARTITION BY product_id ORDER BY price_date ) AS prev_price
FROM   (
SELECT c.*,
       CASE product_price
       WHEN LEAD( product_price ) OVER ( PARTITION BY product_id ORDER BY price_date )
       THEN NULL
       ELSE product_price
       END AS prev_price
FROM   comp_results c
)

Results:

| PRODUCT_ID |           PRICE_DATE | PRODUCT_PRICE | PREV_PRICE |
|------------|----------------------|---------------|------------|
| BlueLotion | 2018-08-13T00:00:00Z |           7.5 |     (null) |
| BlueLotion | 2018-08-14T00:00:00Z |           7.5 |     (null) |
| BlueLotion | 2018-08-15T00:00:00Z |           7.5 |     (null) |
| BlueLotion | 2018-08-16T00:00:00Z |           6.5 |        7.5 |
| BlueLotion | 2018-08-17T00:00:00Z |           6.5 |        7.5 |
| BlueLotion | 2018-08-18T00:00:00Z |           6.5 |        7.5 |
| BlueLotion | 2018-08-19T00:00:00Z |           7.5 |        6.5 |
| BlueLotion | 2018-08-20T00:00:00Z |           7.5 |        6.5 |
| BlueLotion | 2018-08-21T00:00:00Z |           7.5 |        6.5 |
| BlueLotion | 2018-08-22T00:00:00Z |            10 |        7.5 |
| BlueLotion | 2018-08-23T00:00:00Z |            10 |        7.5 |
| BlueLotion | 2018-08-24T00:00:00Z |            10 |        7.5 |

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95090

A straight-forward approach to this is a subquery in the select clause:

select
  product_id,
  price_date,
  product_price,
  (
    select
      max(before.product_price) keep (dense_rank last order by before.price_date)
    from comp_results before
    where before.product_id = comp_results.product_id
      and before.price_date < comp_results.price_date
      and before.product_price <> comp_results.product_price
  ) as last_price
from comp_results
order by product_id, price_date desc;

Rextester demo: http://rextester.com/HTBXE60602

Upvotes: 0

Related Questions