Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dpais
Yak Posting Veteran
60 Posts |
 Posted - 2007-11-28 : 10:10:22
|
| Table 1week____Part____Demand___Receipts_____Projection48______1x21____200______100__________400 (!)(s1)__(s1=table2.balance-wk48 demand+wk48 receipts)49______1x21____150______2000_________2250(!)(s2)__(s2=wk48 projection-wk49 demand+wk49 receipts)51______1x21____1120_____4000(!)(s3)___5130(!)(s4)__(s4=same logic as above )52______1x21____100______0(!)(s5)______5030(!)(s6)__(s6=same logic as above )Table 2part____balance___minimum____reorder1x21____500_______5000_______1000All quantities with (!) are to be calculated and updated in the table all other quantities are givenI have used S1... s2 etc etc to denote steps to the calculation and update in the tables ... with explanations above and belowS3 = If wk49.projection - wk51.demand < table2.minimum then (round up ((table2.minimum - (wk49.projection - wk51.demand)) / table2.reorder)) * table2.reorder ..... ie. (round up ((5000 - (2250 - 1120)) / 1000)) * 1000 = 4000else0s3 = basically I want ot see how many parts to reorder given the previous weeks projection and the current weeks requirement.If we go onto S5 lets see what happens. ....s5 = If wk51.projection - wk52.demand < table2.minimum then (round up ((table2.minimum - (wk51.projection - wk52.demand)) / table2.reorder)) * table2.reorderelse0Therefore the result will be 0 .. becasue the condition (wk51.projection - wk52.demand < table2.minimum) will give us 5030 which prevents the if-then-else clause from processing and the value will be 0If some of you have guessed this is a mini MRP for those of you who are Supply Chain savvy .... Thanks in advance for the help - i am using SQL 2000 ..... i can replicate this on a Excel Spreadsheet but have had no luck in sql 2000 ....Please help with the calculations and update given that i have both tables and need to automatically calculate and update all values with (!) .Hope my explanations are not too confusing.DP |
|
|
|
|
|
Mini MRP Calculation