Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Recursive function - sql2008 Rate Topic Display Mode Topic Options
Author
 Message
 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_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1
Post #1372186
 Posted Friday, October 12, 2012 9:17 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 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_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1I 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 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_task1 1 1 76 NULL Task 12 1 2 60 NULL Task 1.13 2 3 2 100 Task 1.1.14 2 3 38 NULL Task 1.1.25 4 4 10 100 Task 1.1.2.16 4 4 22 20 Task 1.1.2.27 4 4 5 35 Task 1.1.2.38 4 4 1 40 Task 1.1.2.49 2 3 20 NULL Task 1.1.310 9 4 20 20 Task 1.1.3.111 1 2 9 NULL Task 1.212 11 3 2 0 Task 1.2.113 11 3 7 NULL Task 1.2.214 13 4 2 50 Task 1.2.2.115 13 4 5 50 Task 1.2.2.216 1 2 10 0 Task 1.317 1 2 20 0 Task 1.418 1 2 6 NULL Task 1.519 18 3 6 NULL Task 1.5.120 19 4 6 NULL Task 1.5.1.121 20 5 6 10 Task 1.5.1.1.1
Post #1372258
 Posted Friday, October 12, 2012 10:08 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 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 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 252 1 2 60 NULL Task 1.1 383 2 3 2 100 Task 1.1.1 1004 2 3 38 NULL Task 1.1.2 445 4 4 10 100 Task 1.1.2.1 1006 4 4 22 20 Task 1.1.2.2 207 4 4 5 35 Task 1.1.2.3 358 4 4 1 40 Task 1.1.2.4 409 2 3 20 NULL Task 1.1.3 2010 9 4 20 20 Task 1.1.3.1 2011 1 2 9 NULL Task 1.2 3912 11 3 2 0 Task 1.2.1 013 11 3 7 NULL Task 1.2.2 5014 13 4 2 50 Task 1.2.2.1 5015 13 4 5 50 Task 1.2.2.2 5016 1 2 10 0 Task 1.3 017 1 2 20 0 Task 1.4 018 1 2 6 NULL Task 1.5 1019 18 3 6 NULL Task 1.5.1 1020 19 4 6 NULL Task 1.5.1.1 1021 20 5 6 10 Task 1.5.1.1.1 10
Post #1372265
 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
 Posted Friday, October 12, 2012 10:27 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 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 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 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
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 Your calculation seems have changed since your first post? 21 20 5 6 10 Task 1.5.1.1.1I am trying to create a recurive function.Calculation starts from the bottom(5*6)+10 = 40it goes for the column above this percent_task columnBut 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

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.