jdoconsulting (3/18/2008)
Does anyone have a good example of using a CTE to rollup assembly costs in a nested Bill of Materials? It seems to me that this should be done from the bottom of the assembly upwards. Most examples I've found deal with rolling up salaries for employees in an organization. The difference with a BOM is that an assembly can be part of multiple assemblies so there isn't just one path in the hierarchy like there is with an employee in an organization.
Not sure how you would do that. If you have 2-inch #10 bolts as one of you assembly parts, how do you roll that up? It might be used in hundreds of different end products.
On BoM hierarchies, I usually roll them down. Start at the final product, or the sub-assembly that you want data for, and go down from there.
If you really want to go from the bottom up, you'd just reverse the join from the Books Online example. Instead of joining the Parent ID in the table to the ID in the CTE, join the ID in the table to the Parent ID in the CTE. That gives you a bottom-up hierarchy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon