InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:55:27 GMT20http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxI found the solution and it is working fine. Still to undergo testing. But I think, it is works as expected.Tue, 16 Oct 2012 12:26:47 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxHi,Did you understand the calculation.Does anyone has any approch for this calculation?Tue, 16 Oct 2012 11:10:47 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxYes, calcuation has changed from the first post. I did not understand lmm_value means. Yes the values are from MS-Project.The value of Task 1 is obtained from its children - Task 1.1, Task 1.2, Task 1.3, Task 1.4 and Task 1.5. All these task has the id_father = 1 and Task 1 has id_task = 1. Hence all these 5 are the children of Task 1.(38*60+39*9+0*10+0*20+10*6)/(60+9+10+20+6)2691/105 = 25To find the value of Task 1.1, we need to calculate from its children - task 1.1.1, task 1.1.2, task 1.1.3(100*2+44*38+20*20)/(2+38+20) = 38For ex. Task 1.5 has only one child task 1.5.1 and task 1.5.1 has one children task 1.5.1.1 and task 1.5.1.1 has one children task 1.5.1.1.1. First we need to calculate the value of 1.5.1.1.1 that is 10*6/10 = 10 and this values with be precent_task for its parent task 1.5.1.1 and the values of task 1.5.1.1 will 10*6/6=10 and goes on. Let me know, if this is clear.But I have developed the proceure, it is working almost fine. But, when I debug and check the values, it calulated fine for the first loop and from next loop onwards, the value of Calc and calc1 variables dont change, they remain 0. I dont understand wny this is happening. Here is the code.create Procedure GetCalculationForTestData1( @taskId int, @fatherId int, @taskLevel int) as begin Create table #TestFinal ( id_task int primary key, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24), Calculation int, Calculation1 int ); Insert #TestFinal (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task) select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task from [Reportes].[dbo].[FunctionTST1] declare @Calc int = 0 declare @Calc1 int = 0 declare @IdFat int =0 While(@fatherId <> 0)Begin While(@taskLevel <> 0) Begin Create table #TestData ( id_task int primary key, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24), Calculation int, Calculation1 int ); Insert #TestData (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task) select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task from [Reportes].[dbo].[FunctionTST1] where level_task = @taskLevel and id_fathertask = @fatherId ;with cteOrdered as ( select * from #TestData ) update cteOrdered set @Calc = Calculation = (percent_task * duracion_task) from cteOrdered WITH (TABLOCKX) OPTION (MAXDOP 1) ;with cteOrdered1 as ( select * from #TestData ) update cteOrdered1 set @Calc1 = Calculation1= ((select sum(Calculation) from cteOrdered1) / (select sum(duracion_task) from cteOrdered1)) set rowcount 1 select @IdFat = id_fathertask from #TestData set rowcount 1000 update #TestFinal set percent_task = @Calc1 where id_task = @IdFat drop table #TestData set @taskLevel = @taskLevel -1 set @Calc = 0 set @Calc1 = 0 End set @fatherId = @fatherId - 1 set @Calc = 0 set @Calc1 = 0End select * from #TestFinal EndFri, 12 Oct 2012 15:29:41 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxYour calculation seems have changed since your first post? [quote]21 20 5 6 10 Task 1.5.1.1.1I 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[/quote]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.Fri, 12 Oct 2012 12:32:07 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxThanks a lot for coming forward to help. I really appreciate that. Here are the insert statements. And the values I pass to procedure is (21,20,5). Please let me know, if need any other details.CREATE TABLE [dbo].[test]([id_task] [int] NULL,[id_fathertask] [int] NULL,[level_task] [int] NULL,[duracion_task] [float] NULL,[percent_task] [float] NULL,[name_task] [nvarchar](50) NULL,[Imm_value] [int] Null);insert into [dbo].[test]values(1,1,1,76,NULL,'Task 1',25),(2,1,2,60,NULL,'Task,1.1',38),(3,2,3,2,100,'Task,1.1.1',100),(4,2,3,38,NULL,'Task,1.1.2',44),(5,4,4,10,100,'Task,1.1.2.1',100),(6,4,4,22,20,'Task,1.1.2.2',20),(7,4,4,5,35,'Task,1.1.2.3',35),(8,4,4,1,40,'Task,1.1.2.4',40),(9,2,3,20,NULL,'Task,1.1.3',20),(10,9,4,20,20,'Task,1.1.3.1',20),(11,1,2,9,NULL,'Task,1.2',39),(12,11,3,2,0,'Task,1.2.1',0),(13,11,3,7,NULL,'Task,1.2.2',50),(14,13,4,2,50,'Task,1.2.2.1',50),(15,13,4,5,50,'Task,1.2.2.2',50),(16,1,2,10,0,'Task,1.3',0),(17,1,2,20,0,'Task,1.4',0),(18,1,2,6,NULL,'Task,1.5',10),(19,18,3,6,NULL,'Task,1.5.1',10),(20,19,4,6,NULL,'Task,1.5.1.1',10),(21,20,5,6,10,'Task,1.5.1.1.1',10);Fri, 12 Oct 2012 10:41:16 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxNow, I am thinking, the correct way is to pass the paramenters for Heighest id_father and its corresponding Id_task and level_task.Fri, 12 Oct 2012 10:31:38 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxOK 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.Fri, 12 Oct 2012 10:27:43 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxSorry, I accidently posted without comments.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).like 21, 20, 5 from the table.Fri, 12 Oct 2012 10:25:44 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxid_task id_fathertask level_task duracion_task percent_task name_task 1 1 1 76 NULL Task 1 252 1 2 60 NULL Task 1.1 383 2 3 2 100 Task 1.1.1 1004 2 3 38 NULL Task 1.1.2 445 4 4 10 100 Task 1.1.2.1 1006 4 4 22 20 Task 1.1.2.2 207 4 4 5 35 Task 1.1.2.3 358 4 4 1 40 Task 1.1.2.4 409 2 3 20 NULL Task 1.1.3 2010 9 4 20 20 Task 1.1.3.1 2011 1 2 9 NULL Task 1.2 3912 11 3 2 0 Task 1.2.1 013 11 3 7 NULL Task 1.2.2 5014 13 4 2 50 Task 1.2.2.1 5015 13 4 5 50 Task 1.2.2.2 5016 1 2 10 0 Task 1.3 017 1 2 20 0 Task 1.4 018 1 2 6 NULL Task 1.5 1019 18 3 6 NULL Task 1.5.1 1020 19 4 6 NULL Task 1.5.1.1 1021 20 5 6 10 Task 1.5.1.1.1 10Fri, 12 Oct 2012 10:20:53 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxCan you post the table with the intermediate amounts? It is very unclear what you are trying to do here. Given the procedure you created what would be the values you pass in?Fri, 12 Oct 2012 10:08:57 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxFinal 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.id_task id_fathertask level_task duracion_task percent_task name_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1Fri, 12 Oct 2012 10:00:27 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspx[quote][b]sqlSer12 (10/12/2012)[/b][hr]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.id_task id_fathertask level_task duracion_task percent_task name_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1[/quote]I want to know what you want as output from your query. And to make it legible inserting into a temp table will make it a lot easier to deal with.Fri, 12 Oct 2012 09:17:28 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxTable 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.id_task id_fathertask level_task duracion_task percent_task name_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1Fri, 12 Oct 2012 08:39:57 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxCan you post what you want for desired output? Just create a temp table and hard code inserts into it. I can't understand your explanation very well and the data just has me scratching me head.Fri, 12 Oct 2012 07:55:56 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxThank you so much for your response. I am sorry for changing the code.I cannot combine two update statements, since I need the first update value to calculate the next one.Here is the code and the logic which I am using.CREATE TABLE [dbo].[FunctionTST1]( [id_task] [int] NULL, [id_fathertask] [int] NULL, [level_task] [int] NULL, [duracion_task] [float] NULL, [percent_task] [float] NULL, [name_task] [nvarchar](50) NULL);insert into [dbo].[FunctionTST1]values(1,1,1,76,NULL,'Task 1'),(2,1,2,60,NULL,'Task,1.1'),(3,2,3,2,100,'Task,1.1.1'),(4,2,3,38,NULL,'Task,1.1.2'),(5,4,4,10,100,'Task,1.1.2.1'),(6,4,4,22,20,'Task,1.1.2.2'),(7,4,4,5,35,'Task,1.1.2.3'),(8,4,4,1,40,'Task,1.1.2.4'),(9,2,3,20,NULL,'Task,1.1.3'),(10,9,4,20,20,'Task,1.1.3.1'),(11,1,2,9,NULL,'Task,1.2'),(12,11,3,2,0,'Task,1.2.1'),(13,11,3,7,NULL,'Task,1.2.2'),(14,13,4,2,50,'Task,1.2.2.1'),(15,13,4,5,50,'Task,1.2.2.2'),(16,1,2,10,0,'Task,1.3'),(17,1,2,20,0,'Task,1.4'),(18,1,2,6,NULL,'Task,1.5'),(19,18,3,6,NULL,'Task,1.5.1'),(20,19,4,6,NULL,'Task,1.5.1.1'),(21,20,5,6,10,'Task,1.5.1.1.1');The actual result is 25.37 and if convert into whole numbers during all the calclulations then it is 25.The aim of this is to find the percent_task for task1. Table is kind of a tree with Task1 as the root. Task one has 5 children Task 1.1, Task 1.2, Task 1.3, Task 1.4 and Task 1.5. Task 1.5 has one child Task 1.5.1 and it has one child Task 1.5.1.1. And Task 1.5.1.1 has one child Task 1.5.1.1.1 and so on.Task 1.1.2 has four children Task 1.1.2.1, Task 1.1.2.2, Task 1.1.2.3 and Task 1.1.2.4. and so on in the table.We need to start calculation from the leaf nodes. and result of the calulation replaces the percent_task of its parent.For example to calculate percent_task for Task 1.1.2, we need to use the value of his children.10*100 = 100022*20 = 4405*35 = 1751*40 = 40-- ---------38 16551655/38 = 43.55(44) = percent_task of task 1.1.2Hence the values of Task 1.1 = 38, Task 1.2 = 38.8, Task 1.3 = 0, Task 1.4 = 0 and Task 1.5 = 10 and final value is 25Hope the above one is understandable. I do not have much exp in this. Hence I developed the code from whatever you pasted and googling.Thu, 11 Oct 2012 22:23:09 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxI am guessing that you didn't read through the article I suggested and understand this code. You can't reference the cte by multiple statements after it is declared. You need to combine your two update statements. I noticed you also removed the order by in the cte. This order is MANDATORY or you don't know what order the results will be returned. And why oh why did you put this into nested while loops??? You totally destroyed any chance at making this remotely quick. You need to start over here.Can you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? There is no need to put this in nested loops.Thu, 11 Oct 2012 21:21:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxThanks a lot. However, the requirement got changed and had to modify the procedure. This is the procalter Procedure GetCalculationForTestData( @taskId int, @fatherId int, @taskLevel int) as begin Create table #TestFinal ( id_task int primary key, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24), Calculation int, Calculation1 int ); Insert #TestFinal (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task) select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task from [Reportes].[dbo].[FunctionTST1] While(@fatherId <> 0)Begin While(@taskLevel <> 0) Begin Create table #TestData ( id_task int primary key, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24), Calculation int, Calculation1 int ); Insert #TestData (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task) select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task from [Reportes].[dbo].[FunctionTST1] where level_task = @taskLevel and id_fathertask = @fatherId declare @Calc int = 0 declare @Calc1 int = 0 declare @temp int = 0 ;with cteOrdered as ( select * from #TestData ) update cteOrdered set @Calc = Calculation = (percent_task * duracion_task) from cteOrdered WITH (TABLOCKX) OPTION (MAXDOP 1) update cteOrdered set @Calc1 = Calculation1 = sum(Calculation)/sum(duracion_task) from cteOrdered WITH (TABLOCKX) OPTION (MAXDOP 1) update #TestFinal set percent_task = @Calc1 where @taskId = @fatherId drop table #TestData set @temp = @taskLevel -1 execute GetCalculationForTestData1 @taskId,@fatherId,@temp End set @temp=0 set @temp = @fatherId - 1 execute GetCalculationForTestData1 @taskId,@temp,@taskLevelEnd select * from #TestFinal EndBut I am having one error on line 62. Invalid object name 'cteOrdered'. Can anyone help me to solve this?Thu, 11 Oct 2012 17:54:30 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspx[quote][b]sqlSer12 (10/9/2012)[/b][hr]Thanks, I am going through your link. But we can pass the parameter for the function.[/quote]I put together an example of this using the quirky update method. Since you never posted any ddl or sample data I used what Lynn posted.The code below works on the example you provided. I also tried for Task,1.2.2 but I have no idea if it does what you want.[code]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestData]') AND type in (N'U'))DROP TABLE [dbo].TestDataGOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCalculationForTestData]') AND type in (N'P'))DROP PROCEDURE [dbo].[GetCalculationForTestData]goCreate table dbo.TestData ( id_task int, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24));goinsert into dbo.TestDatavalues(1,1,1,76,NULL,'Task 1'),(2,1,2,60,NULL,'Task,1.1'),(3,2,3,2,100,'Task,1.1.1'),(4,2,3,38,NULL,'Task,1.1.2'),(5,4,4,10,100,'Task,1.1.2.1'),(6,4,4,22,20,'Task,1.1.2.2'),(7,4,4,5,35,'Task,1.1.2.3'),(8,4,4,1,40,'Task,1.1.2.4'),(9,2,3,20,NULL,'Task,1.1.3'),(10,9,4,20,20,'Task,1.1.3.1'),(11,1,2,9,NULL,'Task,1.2'),(12,11,3,2,0,'Task,1.2.1'),(13,11,3,7,NULL,'Task,1.2.2'),(14,13,4,2,50,'Task,1.2.2.1'),(15,13,4,5,50,'Task,1.2.2.2'),(16,1,2,10,0,'Task,1.3'),(17,1,2,20,0,'Task,1.4'),(18,1,2,6,NULL,'Task,1.5'),(19,18,3,6,NULL,'Task,1.5.1'),(20,19,4,6,NULL,'Task,1.5.1.1'),(21,20,5,6,10,'Task,1.5.1.1.1');gocreate Procedure GetCalculationForTestData( @TaskName varchar(50)) as begin Create table #TestData ( id_task int primary key, --MUST have a clustered index for the running total to work id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24), Calculation int --need to store the calculation so we added this extra column ); Insert #TestData (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task) select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task from TestData where name_task like @TaskName + '%' declare @Calc int = 0 ;with cteOrdered as ( select top 2147483648 * from #TestData order by id_fathertask desc ) update cteOrdered set @Calc = Calculation = (level_task * duracion_task) + isnull(percent_task, @Calc) from cteOrdered WITH (TABLOCKX) OPTION (MAXDOP 1) select * from #TestData order by id_fathertask descendgoexec GetCalculationForTestData @TaskName = 'Task,1.2.2'exec GetCalculationForTestData @TaskName = 'Task,1.5'[/code]If you use this make sure you read the running totals article I referenced and understand what it is doing. You are the one who is going to have to support this at 3am when it crashes in production.Tue, 09 Oct 2012 12:10:00 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxThanks, I am going through your link. But we can pass the parameter for the function.Tue, 09 Oct 2012 11:08:34 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxYour data structure for this is going to cause you nothing but grief. How do you know what the "root" of the task is?This is a running total type of problem but at the base is this denormalized table. You don't need a recursive function for this. Take a look at this article from Jeff Moden on running totals. [url=http://www.sqlservercentral.com/articles/T-SQL/68467/]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Tue, 09 Oct 2012 08:33:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxThanks for your response.Well, I am trying to have a recursive function for the below calculationCalculation starts fromt the last task 1.5.1.1.1(level_task*duaration_task) + percent_task (5*6)+10 = 40the result goes to percent_task of above task that is 1.5.1.1in 1.5.1.1 (4*6)+40(result from the below) = 64goes like that untill task 1.5, where 1.5 will have 94 as the value.From 1.4 new calaculation, since it doesnt have any child task value will be 40For task 1.3 = 20like that for task 1.2 = 123In the End we need to add the task 1.5+1.4.+1.3+1.2+1.1I hope, this is more understandable.Tue, 09 Oct 2012 08:17:54 GMTsqlSer12http://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxI haven't been able to quite figure out what it is you want in the end, but the following code traverses your data in the proper order starting at the top.Based on this, what is it you are trying to accomplish?[code="sql"]Create table dbo.TestData ( id_task int, id_fathertask int, level_task int, duracion_task int, percent_task int null, name_task varchar(24));goinsert into dbo.TestDatavalues(1,1,1,76,NULL,'Task 1'),(2,1,2,60,NULL,'Task,1.1'),(3,2,3,2,100,'Task,1.1.1'),(4,2,3,38,NULL,'Task,1.1.2'),(5,4,4,10,100,'Task,1.1.2.1'),(6,4,4,22,20,'Task,1.1.2.2'),(7,4,4,5,35,'Task,1.1.2.3'),(8,4,4,1,40,'Task,1.1.2.4'),(9,2,3,20,NULL,'Task,1.1.3'),(10,9,4,20,20,'Task,1.1.3.1'),(11,1,2,9,NULL,'Task,1.2'),(12,11,3,2,0,'Task,1.2.1'),(13,11,3,7,NULL,'Task,1.2.2'),(14,13,4,2,50,'Task,1.2.2.1'),(15,13,4,5,50,'Task,1.2.2.2'),(16,1,2,10,0,'Task,1.3'),(17,1,2,20,0,'Task,1.4'),(18,1,2,6,NULL,'Task,1.5'),(19,18,3,6,NULL,'Task,1.5.1'),(20,19,4,6,NULL,'Task,1.5.1.1'),(21,20,5,6,10,'Task,1.5.1.1.1');gowith BaseData as (select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task, task_level = 1 -- for comparision purposesfrom dbo.TestDatawhere id_task = id_fathertaskunion allselect td.id_task, td.id_fathertask, td.level_task, td.duracion_task, td.percent_task, td.name_task, task_level = bd1.task_level + 1from dbo.TestData td inner join BaseData bd1 on (td.id_fathertask = bd1.id_task and td.id_task <> td.id_fathertask))select * from BaseData order by name_task;godrop table dbo.TestData;go[/code]Mon, 08 Oct 2012 17:33:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1370081-391-1.aspxHi,I am new to sql server 2008 functions, I am facing an issue and request, if some can help.I have a table likeid_task id_fathertask level_task duracion_task percent_task name_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1I am trying to create a recurive function. Calculation starts from the bottom(5*6)+10 = 40it goes for the column above this percent_task columnthen 4+(6*65)until the name_task is same (untill 1.5, starting from 1.5.1... to 1.5)when it changes 1.4..then new caluculationand at the end we need to do the sumsum of ( 1.5+ 1.4+1.3.....like that..I am trying to create a recursive function, but no luck.Create function [dbo].[test1](@p_id_task int)RETURNS @output TABLE(data VARCHAR(256))begindeclare @i intwhile @i in ( select top 100 id_task from [dbo].[test1](@p_id_task) where id_task<=@p_id_task and id_fathertask>=1 and status is null order by id_task desc ) begin DECLARE @start INT select @start = (level_task*duracion_task)+ COALESCE(percent_task,0) from [Reportes].[dbo].[FunctionTST1] where status='Processed' and id_task=@i and status is null INSERT INTO @output (data) values (@start) end return endCan anyone help me on this?Mon, 08 Oct 2012 15:19:58 GMTsqlSer12