Parent Child cost calculation

Question

question

Vikt asked Jan 18, '22

Parent Child cost calculation

Hello everyone,

I have a parent child relationship table. Using which I created another table with levels, cost and qty.

Now I am trying to create a recursive query to calculate the cost for part/assembly at Level 1.

P4 at level 3 has S2,S3 and P5 as child. So sum(cost*qty) for P4 at level 3 is $200.

P4 at level 2 is child of P2 and has it's individual cost of $3.

Also, all real data is on a different production server, so I can't share it. Apologies.

Also to add, I have close to 30K parts/child relationship in my database.

So intention behind creating levels was to save time.

I hope I have shared all required information.

In case you need more details please let me know.

Thank you.

sqlserver2012

capture01-18-1.png (21.6 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Jan 20, 2022 at 08:19 AM

It might just be how you've created your example data, but it's confusing how the same part has different costs. S2 as a sub-part of P4 costs $10, but S2 as a sub-part of P2 costs $1 - is that correct?

0 ·

Answer 1 · 2022-01-23T18:50:52Z

Jeff Moden answered Jan 23, '22

Please see the following article for one way to develop such a thing. And, the 54 second performance on the million rows was a decade ago on a much earlier machine. On today's machines, it takes 19 seconds for the million rows.

https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question