Link to home
Start Free TrialLog in
Avatar of countrymeister
countrymeister

asked on

Update Previous Price for datatable

I have a table
ModelID,
Code,
BusDate,
Price
PrevPrice


I need to update the PrevPrice by getting the Price value for a given ModelID, Code from the previous BusDate.

The tables primary key is ModelID, Code, BusDate

Example
ModelID, Code, BusDate ,     Price,   PrevPrice
1 ,            'ABX', 10/18/2013 ,  7.00  ,   0
1 ,            'ABX', 10/18/2013 ,  9.00  ,   0
1 ,            'ABX', 10/21/2013 ,  10.00 ,   0
1 ,            'ABX', 10/22/2013 ,  8.00  ,   0

In the above example I would the data after the update statement to be
1 ,            'ABX', 10/18/2013 ,  7.00  ,   0
1 ,            'ABX', 10/18/2013 ,  9.00  ,   7.00
1 ,            'ABX', 10/21/2013 ,  10.00 ,   9.00
1 ,            'ABX', 10/22/2013 ,  8.00  ,   10.00
Avatar of awking00
awking00
Flag of United States of America image

Can we assume a time value is present for the busdate? Otherwise, how would you distinguish
1 ,            'ABX', 10/18/2013 ,  7.00  ,   0
1 ,            'ABX', 10/18/2013 ,  9.00  ,   7.00
from
1 ,            'ABX', 10/18/2013 ,  9.00  ,   0
1 ,            'ABX', 10/18/2013 ,  7.00  ,   9.00
?
Avatar of countrymeister
countrymeister

ASKER

Sorry, my example should have had the following, since ModelID, Code and BusDate are the primary key

ModelID, Code, BusDate ,     Price,   PrevPrice
1 ,            'ABX', 10/17/2013 ,  7.00  ,   0
1 ,            'ABX', 10/18/2013 ,  9.00  ,   0
1 ,            'ABX', 10/21/2013 ,  10.00 ,   0
1 ,            'ABX', 10/22/2013 ,  8.00  ,   0

In the above example I would the data after the update statement to be
1 ,            'ABX', 10/17/2013 ,  7.00  ,   0
1 ,            'ABX', 10/18/2013 ,  9.00  ,   7.00
1 ,            'ABX', 10/21/2013 ,  10.00 ,   9.00
1 ,            'ABX', 10/22/2013 ,  8.00  ,   10.00
SQL> select * from yourtable;
   MODELID COD BUSDATE        PRICE  PREVPRICE
---------- --- --------- ---------- ----------
         1 ABX 17-OCT-13          7          0
         1 ABX 18-OCT-13          9          0
         1 ABX 21-OCT-13         10          0
         1 ABX 22-OCT-13          8          0

SQL> merge into yourtable y
  2  using
  3  (select modelid, code, busdate, price,
  4   lag(price) over (partition by modelid, code order by busdate) prevprice
  5   from yourtable) x
  6  on (y.modelid = x.modelid and y.code = x.code and y.busdate = x.busdate)
  7  when matched then
  8  update set y.prevprice = nvl(x.prevprice,0);

4 rows merged.
SQL> select * from yourtable;
   MODELID COD BUSDATE        PRICE  PREVPRICE
---------- --- --------- ---------- ----------
         1 ABX 17-OCT-13          7          0
         1 ABX 18-OCT-13          9          7
         1 ABX 21-OCT-13         10          9
         1 ABX 22-OCT-13          8         10
I cannot use lag as it is a 2012 function. I am using sql 2008
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial