Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive function - sql2008


Recursive function - sql2008

Author
Message
sqlSer12
sqlSer12
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
Hi,
I am new to sql server 2008 functions, I am facing an issue and request, if some can help.
I have a table like

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

then

4+(6*65)

until the name_task is same (untill 1.5, starting from 1.5.1... to 1.5)

when it changes 1.4..then new caluculation

and at the end we need to do the sum

sum of ( 1.5+ 1.4+1.3.....like that..

I am trying to create a recursive function, but no luck.


Create function [dbo].[test1](@p_id_task int)

RETURNS @output TABLE(data VARCHAR(256))

begin
declare @i int
while @i in ( select top 100 id_task from [dbo].[test1](@p_id_task) where id_task<=@p_id_task and id_fathertask>=1 and status is null
order by id_task desc
)
begin
DECLARE @start INT
select @start = (level_task*duracion_task)+ COALESCE(percent_task,0) from [Reportes].[dbo].[FunctionTST1] where status='Processed' and
id_task=@i and status is null

INSERT INTO @output (data) values (@start)

end
return
end

Can anyone help me on this?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24261 Visits: 37982
I haven't been able to quite figure out what it is you want in the end, but the following code traverses your data in the proper order starting at the top.

Based on this, what is it you are trying to accomplish?



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

with BaseData as (
select
id_task,
id_fathertask,
level_task,
duracion_task,
percent_task,
name_task,
task_level = 1 -- for comparision purposes
from
dbo.TestData
where
id_task = id_fathertask
union all
select
td.id_task,
td.id_fathertask,
td.level_task,
td.duracion_task,
td.percent_task,
td.name_task,
task_level = bd1.task_level + 1
from
dbo.TestData td
inner join BaseData bd1
on (td.id_fathertask = bd1.id_task and td.id_task <> td.id_fathertask)
)
select * from BaseData order by name_task;
go

drop table dbo.TestData;
go





Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sqlSer12
sqlSer12
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
Thanks for your response.

Well, I am trying to have a recursive function for the below calculation

Calculation starts fromt the last task 1.5.1.1.1
(level_task*duaration_task) + percent_task (5*6)+10 = 40
the result goes to percent_task of above task that is 1.5.1.1
in 1.5.1.1 (4*6)+40(result from the below) = 64

goes like that untill task 1.5, where 1.5 will have 94 as the value.

From 1.4 new calaculation, since it doesnt have any child task value will be 40

For task 1.3 = 20

like that for task 1.2 = 123

In the End we need to add the task 1.5+1.4.+1.3+1.2+1.1

I hope, this is more understandable.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
Your data structure for this is going to cause you nothing but grief. How do you know what the "root" of the task is?

This is a running total type of problem but at the base is this denormalized table. You don't need a recursive function for this. Take a look at this article from Jeff Moden on running totals. http://www.sqlservercentral.com/articles/T-SQL/68467/

_______________________________________________________________

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)
sqlSer12
sqlSer12
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
Thanks, I am going through your link.

But we can pass the parameter for the function.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
sqlSer12 (10/9/2012)
Thanks, I am going through your link.

But we can pass the parameter for the function.


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.

_______________________________________________________________

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)
sqlSer12
sqlSer12
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
Thanks a lot. However, the requirement got changed and had to modify the procedure. This is the proc

alter Procedure GetCalculationForTestData
(
@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]

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

declare @Calc int = 0
declare @Calc1 int = 0
declare @temp int = 0

;with cteOrdered as
(
select * from #TestData
)

update cteOrdered
set @Calc = Calculation = (percent_task * duracion_task)
from cteOrdered WITH (TABLOCKX)
OPTION (MAXDOP 1)

update cteOrdered
set @Calc1 = Calculation1 = sum(Calculation)/sum(duracion_task)
from cteOrdered WITH (TABLOCKX)
OPTION (MAXDOP 1)

update #TestFinal set percent_task = @Calc1 where @taskId = @fatherId
drop table #TestData
set @temp = @taskLevel -1
execute GetCalculationForTestData1 @taskId,@fatherId,@temp
End
set @temp=0
set @temp = @fatherId - 1
execute GetCalculationForTestData1 @taskId,@temp,@taskLevel
End
select * from #TestFinal

End

But I am having one error on line 62. Invalid object name 'cteOrdered'. Can anyone help me to solve this?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
I am guessing that you didn't read through the article I suggested and understand this code. You can't reference the cte by multiple statements after it is declared. You need to combine your two update statements. I noticed you also removed the order by in the cte. This order is MANDATORY or you don't know what order the results will be returned. And why oh why did you put this into nested while loops??? You totally destroyed any chance at making this remotely quick. You need to start over here.

Can you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? There is no need to put this in nested loops.

_______________________________________________________________

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)
sqlSer12
sqlSer12
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
Thank you so much for your response. I am sorry for changing the code.

I cannot combine two update statements, since I need the first update value to calculate the next one.

Here is the code and the logic which I am using.

CREATE TABLE [dbo].[FunctionTST1](
[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
);

insert into [dbo].[FunctionTST1]
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');

The actual result is 25.37 and if convert into whole numbers during all the calclulations then it is 25.

The aim of this is to find the percent_task for task1. Table is kind of a tree with Task1 as the root. Task one has 5 children Task 1.1, Task 1.2, Task 1.3, Task 1.4 and Task 1.5. Task 1.5 has one child Task 1.5.1 and it has one child Task 1.5.1.1. And Task 1.5.1.1 has one child Task 1.5.1.1.1 and so on.

Task 1.1.2 has four children Task 1.1.2.1, Task 1.1.2.2, Task 1.1.2.3 and Task 1.1.2.4. and so on in the table.

We need to start calculation from the leaf nodes. and result of the calulation replaces the percent_task of its parent.

For example to calculate percent_task for Task 1.1.2, we need to use the value of his children.

10*100 = 1000
22*20 = 440
5*35 = 175
1*40 = 40
-- ---------
38 1655

1655/38 = 43.55(44) = percent_task of task 1.1.2

Hence the values of Task 1.1 = 38, Task 1.2 = 38.8, Task 1.3 = 0, Task 1.4 = 0 and Task 1.5 = 10 and final value is 25

Hope the above one is understandable. I do not have much exp in this. Hence I developed the code from whatever you pasted and googling.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16639 Visits: 17027
Can you post what you want for desired output? Just create a temp table and hard code inserts into it. I can't understand your explanation very well and the data just has me scratching me head.

_______________________________________________________________

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search