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.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestData]') AND type in (N'U'))

DROP TABLE [dbo].TestData

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCalculationForTestData]') AND type in (N'P'))

DROP PROCEDURE [dbo].[GetCalculationForTestData]

go

Create table dbo.TestData (

id_task int,

id_fathertask int,

level_task int,

duracion_task int,

percent_task int null,

name_task varchar(24)

);

go

insert into dbo.TestData

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');

go

create 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 desc

end

go

exec GetCalculationForTestData @TaskName = 'Task,1.2.2'

exec GetCalculationForTestData @TaskName = 'Task,1.5'

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.