multiply value from column where day+2

Question

I have a table like this:

date       | cpo       | production_ms | cpo_sell_profit
=======================================================
2016-08-01 | 7146      | 75187         |  
2016-08-02 | 7299      | 68925         |  
2016-08-03 | 7330      | 65534         |  
2016-08-04 | 7416      | 72133         |  
2016-08-05 | 7563      | 71442         |  

I want to fill the cpo_sell_profit column but I don't know how to multiply the production_ms value with value from cpo where cpo.date = day + 2 in order to fill the cpo_sell_profit.

example:

75187 from 2016-08-01 multiply with 7330 from 2016-08-03

thanks in advance

Answer 1

;with cte as (
    Select *
          ,NewVal = production_ms* Lead(cpo,2,0) over (Order By date)
     From  YourTable
)
Update cte Set cpo_sell_profit = NewVal

Select * from YourTable

Returns

date        cpo     production_ms   cpo_sell_profit
2016-08-01  7146    75187           551120710
2016-08-02  7299    68925           511147800
2016-08-03  7330    65534           495633642
2016-08-04  7416    72133           0
2016-08-05  7563    71442           0

Not sure what you want to do with the records out-of-scope. Currently set to zero, but if you put 1, you would get production_ms .. see Lead(cpo,2,0)