This query gives me syntax error in whenbetween line. how can i solve them?
alter FUNCTION [dbo].[fn_GetActivityLogsArranger]
(
@time AS nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @Return varchar(30)
select @Return = case @time
when between '15:00' and '15:30' then '15:0015:30'
when between '15:30' and '16:00' then '15:3016:00'
when between '16:00' and '16:30' then '16:0016:30'
when between '16:00' and '16:30' then '16:0016:30'
when between '16:30' and '17:00' then '16:3017:00'
when between '17:00' and '17:30' then '17:0017:30'
when between '17:30' and '18:00' then '17:3018:00'
else 'Unknown'
Return @Return
end

You can't use that format of the case syntax. You will have to do a case which does checks:
select @Return = case when @time between '15:00' and '15:30' then '15:0015:30' when @time between '15:30' and '16:00' then '15:3016:00' when @time between '16:00' and '16:30' then '16:0016:30' when @time between '16:00' and '16:30' then '16:0016:30' when @time between '16:30' and '17:00' then '16:3017:00' when @time between '17:00' and '17:30' then '17:0017:30' when @time between '17:30' and '18:00' then '17:3018:00' else 'Unknown' END Return @Return
Also, you were missing an END at the end of your case statement (see END in uppercase above).

Well for starters you need to pass your @variable in each when statement
select @Return = case when @time between ('15:00' and '15:30') then '15:0015:30' when @time between ('15:30' and '16:00') then '15:3016:00' when @time between ('16:00' and '16:30') then '16:0016:30' when @time between ('16:00' and '16:30') then '16:0016:30' when @time between ('16:30' and '17:00') then '16:3017:00' when @time between ('17:00' and '17:30') then '17:0017:30' when @time between ('17:30' and '18:00') then '17:3018:00' else 'Unknown'

alter FUNCTION [dbo].[fn_GetActivityLogsArranger] ( @time AS varchar(30) ) RETURNS varchar(30)AS BEGIN declare @Return varchar(30) select @Return = case when @time between '15:00' and '15:30' then '15:0015:30' when @time between '15:30' and '16:00' then '15:3016:00' when @time between '16:00' and '16:30' then '16:0016:30' when @time between '16:00' and '16:30' then '16:0016:30' when @time between '16:30' and '17:00' then '16:3017:00' when @time between '17:00' and '17:30' then '17:0017:30' when @time between '17:30' and '18:00' then '17:3018:00' else 'Unknown' end Return @Return end
Mitch Wheat : would downvoter mind leaving a comment please. ThanksPhsika : Thank alot. you are correct. Look please another ques.http://stackoverflow.com/questions/829089/howtocalluserdefinedfunctioninordertousewithselectgroupbyorderby 
You need to have the variable in each WHEN clause eg.
case when @time between '15:00' and '15:30' then '15:0015:30' when @time between '15:30' and '16:00' then '15:3016:00'

case syntax : CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

You should not use this function, you should have a table or table valued function of time buckets so you can do a pure join against it. See my other post for an example. The join will outperform by far the approach of calling your function on a row set.
0 comments:
Post a Comment