Hi all,
I have a table that contain a list of accounts in a hierarchical format. (for example, level 1 to 5) The account table looks like this:-
Level 1
| Level 2 | Level 3 | Level 4 | Level 5 |
|---|
| 11100 | 11100 | 11100 | 11000 | 10000 |
| 11200 | 11200 | 11200 | 11000 | 10000 |
| 11300 | 11300 | 11300 | 11000 | 10000 |
| 12100 | 12100 | 12100 | 12000 | 10000 |
| 12200 | 12200 | 12200 | 12000 | 10000 |
Level 1 is the lowest level and Level 5 is the highest. Level 1 is linked to the fact table. Now, my question is if I'm given an account number without knowing the level, how can I find the accounts of the lowest level?
For example (Based on the above sample):
If account 11000 is given, the accounts that should be returned are 11100, 11200, 11300.
If account 10000 is given, the accounts that should be returned are 11100, 11200, 11300, 12100, 12200.
If account 12100 is given, the accounts that should be returned are 12100.
Some note:-
- account number is unique
- there is not pattern in the account number. All account numbers are randomly created.
