I have a table which stores Hierarchy of Customers i.e., GrandParent, Parent and Child as follows
CustomerNum Amount GrantParent Parent
----------- ------ ----------- ------
8046026507 100 NULL 1872539355
8099032159 100 1872539355 8046026507
1872539355 100 NULL NULL
I need a SQL query to get the Sum of AMOUNT when I pass a Customer based on the Hierarchy.
For Example When I pass customer ‘1872539355’(GrandParent) I should get ‘300’ (Sum of all parent, child) of that customer. When I pass ‘8046026507’(Parent) I should get ‘200’. When I pass 8099032159(Child) I should get ‘100’. One Grand Parent Can have multiple Parents and One Parent Can have multiple childs.
Many thanks in Advance.