Click here to Skip to main content
15,894,343 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
SQL
alter function dbo.anf
(
@mdvn int,
@fdate datetime,
@tdate datetime
)
returns @tab table(DVN_CD int,PHC_CD int,HSC_CD int,ANC int)
AS
Begin
declare @vdate date
declare @fedddate date
declare @tedddate date
declare @tvdate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @ANC int
set @ANC=ANC
select dvn_cd,phc_cd,hsc_cd,SUM(ANC) as ANC from
(
	select dvn_cd,phc_cd,hsc_cd,
	case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0  end as ANC 	 
	from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
return @tab
end
Posted
Updated 25-Dec-13 18:15pm
v3
Comments
♥…ЯҠ…♥ 24-Dec-13 8:15am    
What kind of help you want here?
vignezroz 24-Dec-13 8:17am    
I get an error
Msg 102, Level 15, State 31, Procedure anf, Line 38
Incorrect syntax near 'BEGIN'.
vignezroz 24-Dec-13 8:17am    
I want my query function to return a table

User AS key word before Begin, It should solve this problem
 
Share this answer
 
Comments
vignezroz 26-Dec-13 0:17am    
I hav modified my query lik tis!! yet it shows d error
Msg 178, Level 15, State 1, Procedure anf, Line 26
A RETURN statement with a return value cannot be used in this context.
Hi Vignez,

Try this
SQL
alter function dbo.anf
(
@mdvn int,
@fdate datetime,
@tdate datetime
)
returns table
AS
Begin

Add AS keyword before Begin keyword
Hope this helps you a bit.

Regards,
RK
 
Share this answer
 
Comments
vignezroz 26-Dec-13 0:17am    
I hav modified my query lik tis!! yet it shows d error
Msg 178, Level 15, State 1, Procedure anf, Line 26
A RETURN statement with a return value cannot be used in this context.
♥…ЯҠ…♥ 26-Dec-13 1:09am    
You cant do it like that, just put return as table thats enough like the one i mentioned above

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900