how to use the return values of two functions in a select statement
-
Hi
This is my first post
. I have a problem. I have created two functions named dbo.RAN_Posting_Duration(id_number, '01 aug 2005' '31 aug 2005') and dbo.CourseAttendance_duration(id_number, '01 aug 2005' '31 aug 2005'). The dates mentioned in the arguments are from and to dates. Each of these functions calculcate something and return an integer.
Now my question is how to use these return values to sum up and return in a select statement? like sum(function1, function2) ??? i dont think it is working.
I want the sum of the return values of these two functions to be returned.
Please help me cos am desperate
. Thanks.
-
Have you tried?
select dbo.RAN_Posting_Duration(id_number, '01 aug 2005' '31 aug 2005') + dbo.CourseAttendance_duration(id_number, '01 aug 2005' '31 aug 2005')
or
declare @RetValue int
select @RetValue = (dbo.RAN_Posting_Duration(id_number, '01 aug 2005' '31 aug 2005') + dbo.CourseAttendance_duration(id_number, '01 aug 2005' '31 aug 2005'))
-
The actual query goes like this:
create function test(@idnumber varchar(27), @rangestart datetime, @rangeend datetime)
returns int
as
begin
declare @intdays as int
select @intdays = count(distinct date) from daystable as dates
inner join
(select ....'need to use functions here' from which table??
where ...........) as results
on dates.date between results.col1 and results.col2
where dates.date between @rangestart and @rangeend
return @intdays
end
The fact that i will be using the functions to calculate the total number of days in the select statement(function1+function2), which table should i use and what where clause should i add? Obviously the functions dont belong to any table. The sum of the two functions returns an integer.
col1 and col2(in results.col1 and results.col2) are dates which should be the columns of the select statement above (in place of functions). But here i will be using functions i've already written( bcos i need the sum of the return values to be returned).
am confused... can anybody help? i guess i'm a bad explainer..
The real question is i need to calculate the total number of posting duration days and course attendance duration and the days should be within @rangestart and @rangeend which are the range dates (ex 01 aug 2005, 31 aug 2005). I've already written functions for posting and course. please help............
thanks
-
Welcome to the club... the not enough coffee club
. -
I would assume Ray's answer will work.
or--------------------------
Create a new function cobining these two functions
create function test3(@idnumber varchar(27), @rangestart1 datetime, @rangeend1 datetime,@rangestart2 datetime, @rangeend2 datetime)
returns int
as
begin
declare @intdays as int
select @intdays = SUM(MyVal) FROM
(
SELECT databse.dbo.test(@idnumber,@rangestart1 ,@rangestart1) MyVal
UNION
SELECT databse.dbo.test2(@idnumber,,@rangestart2,@rangestart2)
) A
return @intdays
end
or-------------------------------------
Just use the query
select @intdays = SUM(MyVal) FROM
(
SELECT databse.dbo.test(@idnumber,@rangestart1 ,@rangestart1) MyVal
UNION
SELECT databse.dbo.test2(@idnumber,,@rangestart2,@rangestart2)
) A
Regards,
gova
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply