October 12, 2012 at 10:25 am
Sorry, 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.
October 12, 2012 at 10:27 am
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 10:31 am
Now, I am thinking, the correct way is to pass the paramenters for Heighest id_father and its corresponding Id_task and level_task.
October 12, 2012 at 10:41 am
Thanks 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);
October 12, 2012 at 12:32 pm
Your calculation seems have changed since your first post?
21 20 5 6 10 Task 1.5.1.1.1
I am trying to create a recurive function.
Calculation starts from the bottom
(5*6)+10 = 40
it 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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 3:29 pm
Yes, 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 = 25
To 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) = 38
For 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 = 0
End
select * from #TestFinal
End
October 16, 2012 at 11:10 am
Hi,
Did you understand the calculation.
Does anyone has any approch for this calculation?
October 16, 2012 at 12:26 pm
I found the solution and it is working fine. Still to undergo testing. But I think, it is works as expected.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply