so lets say i have two tables inventory and demand. both contains 3 keys location , matrial and yr_wk. inv contains onhand and demand contains demand quantity. INV data : location , matrial , yr_wk, onhand Demand data : location , matrial , yr_wk,demand
now lets say i have demand for 2 years or so might contains 20 or so weeks form current date and i also have inv present for few of them . for eg
location,matrial,yr_wk,onhand,demand
l1,m1,w1,0,300
l1,m1,w2,500,100
l1,m1,w3,0,500
l1,m1,w4,500,300
l1,m1,w5,200,400
result should be like
location,matrial,yr_wk,onhand,demand
l1,m1,w1,0,0
l1,m1,w2,100,0
l1,m1,w3,0,0
l1,m1,w4,0,100
l1,m1,w5,0,400
I have done this through for two dimensional loops which updated data in each iteration but this is takign hours to run as update is heavy command to run each time in loop
so it there a way to optamize it using joins i tried but few cases are always left injoins