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 |
|
Peter Dutch
Posting Yak Master
127 Posts |
 Posted - 2002-03-26 : 06:52:59
|
| Hi all, I have a problem which I can't seem to solve.Assume a production process with different levels. A product has several production steps which all have an input product and an output product. The output of one step is the input of a following step.In a table this would look like the following:Level___Step______ArticleID___Perc____Rend____Type__KG4_______9_________19__________0.5_____1_______2_____1020.414_______9_________15__________0.5_____1_______1 4_______9_________11__________0.5_____1_______13_______8_________14__________1_______1_______13_______8_________15__________0.6_____1_______23_______8_________17__________0.4_____1_______2 2_______7_________13__________1_______1.08____12_______7_________14__________1_______1.08____22_______6_________4___________1_______0.9_____12_______6_________13__________1_______0.9_____21_______5_________11__________1_______1.10____21_______5_________12__________1_______1.10____11_______4_________3___________1_______0.9_____11_______4_________12__________1_______0.9_____2Where Type indicates if the Article is the OUPUT (2) of the production step or the INPUT of that step (1). So the Article with type 2 of the highest level is the finished product (ArticleID 19). We know the output of this product in KG (1020.41). What I need to do is fill the rest of the KG in the table.To fill in the outcome of both INPUT articles of step 9 I would do:PERC * (OUTPUT STEP/REND)0.5 * (1020/1) = 510.20 (Article 15)0.5 * (1020/1) = 510.20 (Article 11)So I can enter the KG of these two articles:Level___Step______ArticleID___Perc____Rend____Type__KG4_______9_________19__________0.5_____1_______2_____1020.414_______9_________15__________0.5_____1_______1_____510.204_______9_________11__________0.5_____1_______1_____510.20Now that I know this, I can fill the KG of the Article 15 & 11 where they are OUPUT articles.For Article 15 this would be in step 8. So I do 0.6 * (510.20/1) = 306.12:Level___Step______ArticleID___Perc____Rend____Type__KG4_______9_________19__________0.5_____1_______2_____1020.414_______9_________15__________0.5_____1_______1_____510.20 4_______9_________11__________0.5_____1_______1_____510.203_______8_________14__________1_______1_______13_______8_________15__________0.6_____1_______2_____306.12(For Article 11 this would be in step 1) and so on until I know all the KG of every article. I guess I need some sort of recursive function to do this, but I can't think of one....Regards,Peter |
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 09:06:32
|
| Have you thought about an UPDATE trigger on the table that would look for update(KG) and then update table set kg for the INPUT articles that are in INSERTED and of type > 1?Jay<O> |
 |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2002-03-26 : 09:08:13
|
| Yes, I thought of that, but the table is a temp table so I can't create a trigger....Peter |
|
|
|
|
|
|
Recursive function???