beeing new til MSSQL I hope someone can point me in the right direction on how to solve this problem:
The DB in question has a table containing a hierarchy of data, pointing back to itself with the root pointing to 0. Simplified it's something like this:
CREATE project (projectID INT PRIMARY KEY, name VARCHAR(64), parentProjectID INT)
Where each project either is the top of a hierarchy structure (parentProjectID = 0 ) or somewhere below the top and pointing to it's parent through parentProjectID ( > 0 ).
In addition, to log time and money spent on each project, another table exist:
CREATE projectlog (projectlogid INT PRIMARY KEY, description VARCHAR(64), cash CURRENCY , time INT, projectID INT)
Needless to say each row in "projectlog" is linked to a row in project through projectID.
I need to generate a report based one particular project, including all (sub)projects _directly_ below it in the hierarchy (where parentProjectID = the ID of the project on which the report is based).
So far so good, but here comes the tricky part:
For each subproject I want to calculate the sums for _all_ cash/time logged, not only for the particular subproject, but for all subprojects below it in the hierarchy.
This is a fairly easy thing to write on the client side, but I want the DB-server to return it from a sql statement if at all possible. I assume a (recursive) stored procedure would be the right way to go about this, but any suggestions on how to solve this is very much appreciated (I haven't dug much into stored procedures yet, but I fear I have to in order to solve this problem).