Fetch/Update the amount value from previous record based on date

Question

I have this table named Pricing. Below is the ddl.

CREATE TABLE PRICING (ITEM          VARCHAR2(80) NOT NULL,
                      LOCATION      NUMBER(10)   NOT NULL,
                      PRICE_DATE    DATE         NOT NULL,
                      PRICE_TYPE    VARCHAR2(2)  NOT NULL,
                      RETAIL_VALUE  NUMBER(20,4),
                      LAST_RETAIL   NUMBER(20,4) );

This table has a few records as below.

Insert into PRICING  values ('30888273',77,to_date('20-SEP-20','DD-MON-RR'),'0',2,NULL);
Insert into PRICING  values ('30888273',77,to_date('03-MAR-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('06-MAR-23','DD-MON-RR'),'4',3,NULL);
Insert into PRICING  values ('30888273',77,to_date('04-APR-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('10-APR-23','DD-MON-RR'),'4',4,NULL);
Insert into PRICING  values ('30888273',77,to_date('02-MAY-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('08-MAY-23','DD-MON-RR'),'4',5,NULL);
Insert into PRICING  values ('30888273',77,to_date('30-MAY-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('05-JUN-23','DD-MON-RR'),'4',6,NULL);
Insert into PRICING  values ('30888273',77,to_date('04-JUL-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('05-JUL-23','DD-MON-RR'),'4',7,NULL);
commit;

Check the data from the table using this query.

select * from PRICING order by price_date;

Output is like this -

What I want is this -

i.e. for every record with price_type as 8 the last_retail column gets its value from the previous (datewise) and latest value of retail_value column where price_type is either 0 or 4. Preferably an Update statement to solve the issue.

Answer 1

No need to update your table, you can get this result using the LAG analytical function in a query:

SELECT item
      ,location
      ,price_date
      ,price_type
      ,retail_value
      ,CASE
           WHEN price_type = 8 THEN
              LAG(retail_value
                  ,1
                  ,0)
              OVER(
                  ORDER BY price_date
              )
           ELSE
              NULL
        END AS last_retail
  FROM pricing;

ITEM         LOCATION PRICE_DATE  PR RETAIL_VALUE LAST_RETAIL
---------- ---------- ----------- -- ------------ -----------
30888273           77 20-SEP-2020 0             2            
30888273           77 03-MAR-2023 8           1.4           2
30888273           77 06-MAR-2023 4             3            
30888273           77 04-APR-2023 8           1.4           3
30888273           77 10-APR-2023 4             4            
30888273           77 02-MAY-2023 8           1.4           4
30888273           77 08-MAY-2023 4             5            
30888273           77 30-MAY-2023 8           1.4           5
30888273           77 05-JUN-2023 4             6            
30888273           77 04-JUL-2023 8           1.4           6
30888273           77 05-JUL-2023 4             7