This is a pretty difficult problem and Im at a lost for how to approach.
Could you help me learn how to approach this problem correct?
------------
High Level:The database tracks motor loads. Via relationships, users are able to assign motors to electrical panels.
Example:
Motor-A and Motor B are supplied with electrical power from electrical Panel-1
Motor-A has a load of 100Watts
Motor-B has a load of 50Watts
Using a aggregation query, I can "group-by" electrical panel and calculate total load for each panel.
Now I know the total motor load on each electrical panel
Figure 1: Summation of Motor Loads for Each Panel
The problem is, Panel-1 also feeds Panel-2 and Panel-3.
I need to do another aggregation query sum for Panel-1 that sums the loads for the motor loads that Panel-1 feeds as well as sums the total loads for Panel-2 and Panel-3
Example:
Motor Loads on Panel-1 = 150Watts
Motor Loads on Panel-2 = 100Watts
Motor Loads on Panel-3 = 100Watts
Total Load for Panel-1 = 350Watts
I have a Table created that tracks the names of the electrical Panels and where each panel gets its power from
Figure 2: Electrical Panel Names and Where they are fed from
------------
I have thought about this for days now. Im very confused at this point. Im not thinking about the problem in the proper steps/framework.
I have designed a aggregation query that provides the total load for each panel already; as seen in Figure 1.
Now, I must do the final step as described above. I am STRUGGLING to form the proper approach to this problem.
How do I do the second step of summing the load for Panel-1 that includes the sum of the motors it provides power to as well as the sum of the power it provides to Panel-2 and Panel-3?
DATABASE CAN BE DOWNLOADED FROM THIS LINK: https://drive.google.com/file/d/1QUC...p=sharingThank you for any help. I hope to learn from the best.