i have 2 tables which is 'ms' and 'fr'.
'ms' contain the pid,month, and totalsales meanwhile 'fr' contain pid, month, n forecast. IN both table, each pid has 12 record based on month.
i actually want to calculate the demand where the formula is = (0.3* totalsales)+(0.7* previous month result of forecast). The previous month result of forecast is like when the user choose pid2 and month 2 , then it will take the forecast result from month 1 as its calculation data.
i tried it already but the desired result is not coming out.
below is my code
select ((0.3*totalsalesamount)+(0.7*forecastdemand)) as demand from Monthlysales, forecastreorder where Monthlysales.P_ID = forecastreorder.Productid and Monthlysales.P_ID = 1 and forecastreorder.Month = 2
when i execute the code above, the result is based on their each forecast result. for example, when i choose pid 1, month 2, then it will take the forecast result from month 2 also. meanwhile i want it to take the forecast result from month 1 as its calculation data.
how can i do that??
please help me, thanks in advanced :)