December 18, 2008 at 11:43 pm
sql create a function to get sum (amount) between start date and end date by passing start date and end date as input parameters to the function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[FN_R2CQ](@START_DATE DATETIME,@END_DATE DATETIME)
RETURNS TABLE
AS
RETURN
SELECT SUM(QTRLY) AS QTR_AMOUNT
FROM QTRLY AS QTR
WHEREQTR_START_DATE BETWEEN @START_DATE AND @END_DATE
i tried this
this is executed successfully
but when i used this function in the INSERT INTO list it gives me an error message
INSERT INTO @QTRLY_ACCOUNTS
(KPI,
DATE,
QTR_AMOUNT
)
SELECTAccountDescription,
DATE,
dbo.QTR_AMOUNT(QTR_START_DATE,QTR_END_DATE)
FROM QTRLY
GROUP BY AccountDescription,DATE
Msg 4121, level 16, state 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.QTR_AMOUNT", or the name is ambiguous.
can any one please help me
December 19, 2008 at 12:23 am
In the alter, the function's called [FN_R2CQ], but you're calling it as dbo.QTR_AMOUNT. Cut and paste issue?
Aside, but also important, that is not going to perform particularly well, especially if there are a lot of rows in the QTRLY table. It may be better to not use a function at all, but to just do the query direct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 6:45 am
You have defined a table valued function and to put it in a select list you want a scalar valued function. Be aware that using this will reduce performance. I think you'd be better off with a derived table that returns the data.
In you example I think this will work:
[font="Courier New"]INSERT INTO @QTRLY_ACCOUNTS
(
KPI,
DATE,
QTR_AMOUNT
)
SELECT
Q.AccountDescription,
Q.DATE,
F.QTR_AMOUNT
FROM
QTRLY Q CROSS Apply
dbo.FN_R2CQ(Q.QTR_START_DATE,Q.QTR_END_DATE) F
GROUP BY
Q.AccountDescription,
Q.DATE
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply