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
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
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
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
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
ASKER
I cannot use lag as it is a 2012 function. I am using sql 2008
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
?