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 that has plagued us. 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 have shown screen dumps of the intermediate results. The document may be found on my website under the SQL Server tab http://www.infogoldusa.com
I have also posted the code and a copy of the SQL Server 2008 database for those of you who wish to try it out. It would be super to hear your thoughts and perhaps share some of your experiences in doing similar concepts.
In part two, I shall be showing a similar technique, but this time utilizing CTE's, so please watch for this posting.
Happy Programming
regards Steve