Link to home
Start Free TrialLog in
Avatar of nutsch
nutschFlag for United States of America

asked on

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.

Thanks for your help,

Thomas
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

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?
Avatar of nutsch

ASKER

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).
should now read:
(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?
Avatar of nutsch

ASKER

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?
Avatar of nutsch

ASKER

Exactly. Thanks for looking into it.

Thomas
SOLUTION
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nutsch

ASKER

That will work for 2 level deep,  but not further than that, would it?
Can you expand on your sample data with something more than two levels deep - and provide what you'd like to see as output?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial