As most of you, we in the financial world have often been faced with the challenge of extracting data from relational sources that within the business frame of reference are more hierarchical in nature. Recently, I did what most of us do from time to time, I searched on “Google” for a nifty method of tackling an age old problem that we have. This was an efficient and effective way in which to extract, display and report upon our customers’ composite fund data.
This may seem a very ordinary task but in our case we often deal with composite funds which have ‘baby funds’ which in turn may have ‘baby funds’ themselves. Our challenge was to find a way to report on the cumulative market value at each level of the hierarchy. I found the skeleton of what I was looking for in an article by Jacob Sebastian ( http://beyondrelational.com/blogs/jacob/default.aspx ) and I have taken the code and modified it to suit our corporate needs.
I have created a Word Document showing step by step the logic behind the code and shown screen dumps of the intermediate results. The document may be found on my website under the SQL Server tab http://www.infogoldusa.com
The actual code has been broken down within the document, but I am more than willing to post the code should anyone want it.