Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Recursive function - sql2008 Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
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_task
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

Post #1372186
Posted Friday, October 12, 2012 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 13,059, Visits: 11,887
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.

id_task id_fathertask level_task duracion_task percent_task name_task
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



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.



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372222
Posted Friday, October 12, 2012 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
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.


id_task id_fathertask level_task duracion_task percent_task name_task
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
Post #1372258
Posted Friday, October 12, 2012 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 13,059, Visits: 11,887
Can 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?

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372261
Posted Friday, October 12, 2012 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
id_task id_fathertask level_task duracion_task percent_task name_task
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
Post #1372265
Posted Friday, October 12, 2012 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
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.
Post #1372268
Posted Friday, October 12, 2012 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 13,059, Visits: 11,887
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372270
Posted Friday, October 12, 2012 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
Now, I am thinking, the correct way is to pass the paramenters for Heighest id_father and its corresponding Id_task and level_task.
Post #1372275
Posted Friday, October 12, 2012 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 12:25 PM
Points: 19, Visits: 46
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);
Post #1372277
Posted Friday, October 12, 2012 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 13,059, Visits: 11,887
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372321
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse