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

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'