March 1, 2016 at 10:59 am
I have 3 functions that return a separate value for each one, i.e.:
CountOpps = Total Opps Created
CountRevWon = Total Revenue on Closed Opps
CountAssRValue = Total Revenue on Assessment Value
I then have a select query which returns multiple other values, how can I add my functions into my select query, as if I put the following:
select dbo.CountOpps(UserID), I get this message:
An insufficient number of arguments were supplied for the function
my function is setup with 3 arguments: User_ID, @StartDate, @EndDate
How do I call this function in my select statement, do I have to specify values?
Please help!!!
March 1, 2016 at 11:17 am
Stix83 (3/1/2016)
I have 3 functions that return a separate value for each one, i.e.:CountOpps = Total Opps Created
CountRevWon = Total Revenue on Closed Opps
CountAssRValue = Total Revenue on Assessment Value
I then have a select query which returns multiple other values, how can I add my functions into my select query, as if I put the following:
select dbo.CountOpps(UserID), I get this message:
An insufficient number of arguments were supplied for the function
my function is setup with 3 arguments: User_ID, @StartDate, @EndDate
How do I call this function in my select statement, do I have to specify values?
Please help!!!
Your answer is in the error.
The function was defined with three arguments, you are calling it with only one argument being supplied.
You would need something like:
select dbo.CountOpps(UserID, '01/01/2016', '01/31/2016')
It also does not sound like a good use for a inline function, but without seeing your schema or code, we can't make that call.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 1, 2016 at 11:18 am
you should just pass all the parameters; if one of the columns is a column name, you have to select it from the table;
select dbo.CountOpps(User_ID, @StartDate, @EndDate),*
FROM SOMETABLE
Lowell
March 1, 2016 at 11:38 am
select statement:
select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
count(A.Owner_Id) as Max
from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id
where b.Resource_Id in ('R25B2864CB6', 'R2691D72523', 'R25B2868972','R25B2868EE0','R39D1B39765', 'R0DF25BF278')
and b.Record_Type = 3
and a.Creator_Id In('AMCKENZIE','ASARAK','CWEIMANN', 'EWOOLDRIDGE', 'GHAVENGA', 'JSTAPELBERG','WILLEMB', 'YRHODA', 'JMALAN')
and A.App_Date between '2016-02-01' and '2016-02-29'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name
Function:
CREATE FUNCTION [dbo].[CountOpps](@UserID varchar(12), @StartDate datetime, @EndDate Datetime)
RETURNS int
AS
-- Returns the number of opportunities created per user per day.
BEGIN
DECLARE @OppCount int;
SELECT @OppCount = Count(AMGR_Opportunity_Tbl.Creator_Id)
FROM dbo.AMGR_Opportunity_Tbl
WHERE Creator_Id = @user-id
AND Convert(Date,Create_Date) between @StartDate and @EndDate
Return @OppCount
END
I don't want to put a specific date range in to the function as I need to pull for certain dates not a set date
March 1, 2016 at 12:00 pm
Stix83 (3/1/2016)
select statement:select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
count(A.Owner_Id) as Max
from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id
where b.Resource_Id in ('R25B2864CB6', 'R2691D72523', 'R25B2868972','R25B2868EE0','R39D1B39765', 'R0DF25BF278')
and b.Record_Type = 3
and a.Creator_Id In('AMCKENZIE','ASARAK','CWEIMANN', 'EWOOLDRIDGE', 'GHAVENGA', 'JSTAPELBERG','WILLEMB', 'YRHODA', 'JMALAN')
and A.App_Date between '2016-02-01' and '2016-02-29'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name
Function:
CREATE FUNCTION [dbo].[CountOpps](@UserID varchar(12), @StartDate datetime, @EndDate Datetime)
RETURNS int
AS
-- Returns the number of opportunities created per user per day.
BEGIN
DECLARE @OppCount int;
SELECT @OppCount = Count(AMGR_Opportunity_Tbl.Creator_Id)
FROM dbo.AMGR_Opportunity_Tbl
WHERE Creator_Id = @user-id
AND Convert(Date,Create_Date) between @StartDate and @EndDate
Return @OppCount
END
I don't want to put a specific date range in to the function as I need to pull for certain dates not a set date
You may need to clarify what you are asking for here. Particularly what you mean by "I don't want to put a specific date range in to the function as I need to pull for certain dates not a set date"
Also, your function could be re-written like this:
CREATE FUNCTION [dbo].[CountOpps](@UserID varchar(12), @StartDate datetime, @EndDate Datetime)
RETURNS int
AS
-- Returns the number of opportunities created per user per day.
BEGIN
RETURN
(
SELECT Count(AMGR_Opportunity_Tbl.Creator_Id)
FROM dbo.AMGR_Opportunity_Tbl
WHERE Creator_Id = @user-id
AND Convert(Date,Create_Date) between @StartDate and @EndDate
)
END;
GO
-- Itzik Ben-Gan 2001
March 1, 2016 at 12:19 pm
If I create my function as below, I get the following error:
Must declare scalar variable "@EndDate"
what I mean by the following is:
I don't want to put in a specific date, I want to be able to pull all dates.
So when I select my function in the select statement, I don't want to put in a specified date eg:
select dbo.countopps (userid,'2016-02-01','2016-02-29')
what I want to achieve in my query is the following:
my select statement returns the total appointment per resource (pop-in,1stmeeting,closing_meeting etc) per user, I want to incorporate the total opps created per user in this table when the results are returned.
I have two SSRS reports running on two different queries, however I want to join the report and I need the easiest way how to do this in the query. One report is running on an SP, and the other on a simple select statement with joins. The SP is made up of functions, I was made aware that I cannot add a SP to a select statement so I now have to add in my functions into the select statement.
March 1, 2016 at 12:52 pm
Stix83 (3/1/2016)
If I create my function as below, I get the following error:Must declare scalar variable "@EndDate"
what I mean by the following is:
I don't want to put in a specific date, I want to be able to pull all dates.
So when I select my function in the select statement, I don't want to put in a specified date eg:
select dbo.countopps (userid,'2016-02-01','2016-02-29')
what I want to achieve in my query is the following:
my select statement returns the total appointment per resource (pop-in,1stmeeting,closing_meeting etc) per user, I want to incorporate the total opps created per user in this table when the results are returned.
I have two SSRS reports running on two different queries, however I want to join the report and I need the easiest way how to do this in the query. One report is running on an SP, and the other on a simple select statement with joins. The SP is made up of functions, I was made aware that I cannot add a SP to a select statement so I now have to add in my functions into the select statement.
You are trying to get an answer without any question! At least not one we can understand.
This may mean something to you:
I want to incorporate the total opps created per user in this table when the results are returned.
What is an opp?
Can you provide the table structure, and some sample data, and the desired output?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 1, 2016 at 1:34 pm
Lowell (3/1/2016)
you should just pass all the parameters; if one of the columns is a column name, you have to select it from the table;
select dbo.CountOpps(User_ID, @StartDate, @EndDate),*
FROM SOMETABLE
You would use something like Lowell showed:
select dbo.CountOpps(S.User_ID, S.StartDate, S.EndDate),s.user_ID
FROM SOMETABLE S
March 1, 2016 at 2:56 pm
Stix83 (3/1/2016)
select statement:select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
count(A.Owner_Id) as Max
from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id
where b.Resource_Id in ('R25B2864CB6', 'R2691D72523', 'R25B2868972','R25B2868EE0','R39D1B39765', 'R0DF25BF278')
and b.Record_Type = 3
and a.Creator_Id In('AMCKENZIE','ASARAK','CWEIMANN', 'EWOOLDRIDGE', 'GHAVENGA', 'JSTAPELBERG','WILLEMB', 'YRHODA', 'JMALAN')
and A.App_Date between '2016-02-01' and '2016-02-29'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name
Function:
CREATE FUNCTION [dbo].[CountOpps](@UserID varchar(12), @StartDate datetime, @EndDate Datetime)
RETURNS int
AS
-- Returns the number of opportunities created per user per day.
BEGIN
DECLARE @OppCount int;
SELECT @OppCount = Count(AMGR_Opportunity_Tbl.Creator_Id)
FROM dbo.AMGR_Opportunity_Tbl
WHERE Creator_Id = @user-id
AND Convert(Date,Create_Date) between @StartDate and @EndDate
Return @OppCount
END
I don't want to put a specific date range in to the function as I need to pull for certain dates not a set date
I may be missing something here, but why are you using the function in the first place? A scalar function that reads data from a table isn't going to do well. It has to execute the function once for every row in the table.
Keep in mind that we don't know your data and without DDL and sample data, this is untested. Will something along these lines work for you?
select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
count(A.Owner_Id) as Max,
COUNT(opp.creator_id) OppCount
from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id
left outer join dbo.AMGR_Opportunity_Tbl opp ON a.create_id = opp.creator_id
where b.Resource_Id in ('R25B2864CB6', 'R2691D72523', 'R25B2868972','R25B2868EE0','R39D1B39765', 'R0DF25BF278')
and b.Record_Type = 3
and a.Creator_Id In ('AMCKENZIE','ASARAK','CWEIMANN', 'EWOOLDRIDGE', 'GHAVENGA', 'JSTAPELBERG','WILLEMB', 'YRHODA', 'JMALAN')
and A.App_Date between '2016-02-01' and '2016-02-29'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name;
The key question is about the join predicate on dbo.AMGR_Opportunity_Tbl. If that's valid, then this might be what you want. You said you don't want to restrict the dates in the dbo.AMGR_Opportunity_Tbl, so I didn't add a predicate in the WHERE clause for it, but you could add one in if you want to.
I hope this helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply