Table looks like below. For Ex. row 3, Id_task = 3, level_task=3 menas it is at level 3, id_fathertask = 2 that means, its parent is the one which has id_task=2, that is the one in the second row and so on. Aim is to calculate the value of the root that is Task 1. The value of Task 1 should be 25 after calculations.

It is kind of project plan and differnt level and sub level inside. id_task is kind of a primary key.

sqlSer12 (10/12/2012)Table looks like below. For Ex. row 3, Id_task = 3, level_task=3 menas it is at level 3, id_fathertask = 2 that means, its parent is the one which has id_task=2, that is the one in the second row and so on. Aim is to calculate the value of the root that is Task 1. The value of Task 1 should be 25 after calculations.

It is kind of project plan and differnt level and sub level inside. id_task is kind of a primary key.

Final result should be 25 or 25.37 to be precise. This the sample table data. Task is calulate the value of root of the tree that is Task1(First row) by using its children. For Ex. thirs row id_task=3 it is unique for every row and identify the task number. level_task = 3, that is it belongs to the third level in the tree. id_fathertask = 2 means, its parent the one which has id_task as 2, that is Task 1.1(second row). The result of the calculation should go to the percent_task of their parent. Hence we start the calculation from the lowest level in the tree and result goes to its parent and finally we find the value of the root.

the last value in the each row is the intermediate result. In the procedure I will pass the parameters as Heighest level and its id_father and id_task. First one is id_task next is id_father and level_task(heighest level available in the tree).

OK one more time, can you post this as inserts to a temp table so I can actually read it? And again what are the values you would pass in to the procedure that you created? Not an explanation because I don't know what "highest" means. What would be the actual values? I am willing to help but you have to help me understand your situation.

I am trying to create a recurive function.Calculation starts from the bottom

(5*6)+10 = 40it goes for the column above this percent_task column

But now in your expected output you have lmm_value as 10???

There is something fundamentally wrong with this whole process. Your id_task has a father of itself which is a bit challenging. The more I am looking at this the more this seems like something exported from MS-Project where Task 1 is the main task and all the rest of them are sub tasks? Unfortunately it is totally unclear to me what you want for output. Obviously I can see the values but I don't understand how you calculate them, and they have changed somewhere along the way.

Can you explain clearly how you get your calculation? Also I am not quite sure how you would get the 25.37 as the output of your proc?? Maybe you just need to approach this differently. I would try something but I have clue what the final value means or how to get it.