need help in implementing a scenario

Database
Enthusiast

need help in implementing a scenario

Here I have a scenario which needs to update a opening inventory with previous available closing inventory.

For example please find table data as below. Now daily i have to run an update on this table checking the opening inventory for 

a particular (date.cust_id) and update with closing inventory from the previous available closing inventory for that cust_id

Target Table (Before Update)

Cust_id  Date               Opening_Inventory  Closing Inventory

1            2013-10-28    0                               20

1            2013-11-01    0                               10

1            2013-11-03    0                               30

2            2013-10-29    0                               40

2            2013-11-02    0                               15

Target Table (After Update)

Cust_id  Date               Opening_Inventory  Closing Inventory

1            2013-10-28    0                               20

1            2013-11-01    20                             10

1            2013-11-03    10                             30

2            2013-10-29    0                               40

2            2013-11-02    40                             15

1 REPLY
Senior Apprentice

Re: need help in implementing a scenario

It's easy to get the previous value with OLAP-functions:

select cust_id, date,
min(Closing_Inventory)
over (partition by cust_id
order by date
rows between 1 preceding and 1 preceding) as Opening_Inventory
from tab
qualify Opening_Inventory is not null

Simply use this query in a UPDATE FROM.