Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
Keep up to date  daily newsletter:
Sign up
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008  General
»
Recursive function  sql2008
23 posts, Page 2 of 3
««
1
2
3
»»
Recursive function  sql2008
Rate Topic
Display Mode
Topic Options
Author
Message
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 8:39 AM
Grasshopper
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
Sean Lange
Sean Lange
Posted Friday, October 12, 2012 9:17 AM
SSCoach
Group: General Forum Members
Last Login: Wednesday, April 27, 2016 1:45 PM
Points: 15,549,
Visits: 15,699
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
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 10:00 AM
Grasshopper
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
Sean Lange
Sean Lange
Posted Friday, October 12, 2012 10:08 AM
SSCoach
Group: General Forum Members
Last Login: Wednesday, April 27, 2016 1:45 PM
Points: 15,549,
Visits: 15,699
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
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 10:20 AM
Grasshopper
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
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 10:25 AM
Grasshopper
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
Sean Lange
Sean Lange
Posted Friday, October 12, 2012 10:27 AM
SSCoach
Group: General Forum Members
Last Login: Wednesday, April 27, 2016 1:45 PM
Points: 15,549,
Visits: 15,699
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
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 10:31 AM
Grasshopper
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
sqlSer12
sqlSer12
Posted Friday, October 12, 2012 10:41 AM
Grasshopper
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
Sean Lange
Sean Lange
Posted Friday, October 12, 2012 12:32 PM
SSCoach
Group: General Forum Members
Last Login: Wednesday, April 27, 2016 1:45 PM
Points: 15,549,
Visits: 15,699
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 MSProject 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 »
23 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 20022016 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.