# Expand SQL Hierarchy with volume share of origin

Hi experts

I have a table with the following fields:
from_lot
from_vol
to_lot
to_vol

I need a query to give me the breakdown of all to_lots, knowing that a from_lot can be a composite of multiple from_lot.

Example:

from_lot                  from_vol                  to_lot                    to_vol
1A                        1500                      A                         1500
1B                        1500                      A                         1500
T2                        NULL                      C                         4457
T3                        NULL                      C                         3902
A                         414                         C                         414

I'd need that with all the to_lot broken down to the smallest component:

to_lot                    from_lot                  volume
A                         1A                        1500
A                         1B                        1500
C                         T2                        4457
C                         T3                        3902
C                         1A                        207
C                         1B                        207

(Since CFHV9 is composed of CFHV9A and B, the CFHV9 portion of CFHV11 gets broken down further). There is no limit to the number of levels.

Thomas
Matt Bowler

I might have missed a meeting here? Where are you getting CFHV9 and CFHV11 from? I can't find that in your example tables anywhere?

Simplified the data and then forgot to update the text. This portion:
(Since CFHV9 is composed of CFHV9A and B, the CFHV9 portion of CFHV11 gets broken down further).
(Since A is composed of 1A and 1B, the A portion of C gets broken down further).
So are you saying that when to_lot C relates to from_lot 1A - that the A part of that from_lot string can get broken down further - into 1A and 1B?

I see an infinite recursion problem here - do you have a halting condition?

Not exactly, in the first table, the following line indicates that A is a component of C (414 gallons worth of A specifically)

from_lot                  from_vol                  to_lot                    to_vol
A                         414                         C                         414

Since the table also has A as a composite lot (of 1A and 1B),  the smallest components of C are T2, T3, 1A and 1B. Since 1A and 1B are not present in the to_lot column, they're at the smallest level. Recursion stops there.

from_lot                  from_vol                  to_lot                    to_vol
1A                        1500                      A                         1500
1B                        1500                      A                         1500

I hope that makes things a little clearer. I basically have extensive wine blends and I'm trying to get back to the initial bulk wine purchase and grape crushing.

Thomas
Thanks Thomas - think I've got this now. :)

I'll try to have a look at a query ASAP - but realistically not until next week. I'm thinking a recursive CTE since we could be going down multiple levels. My understanding is that the halting condition is when you get to a lot that is not in the to_lot column?

Exactly. Thanks for looking into it.

Thomas
SOLUTION
Matt Bowler

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.