Problem: How to make a UDF from the following working code.
declare @currentweek as date
declare @1stweek as date
declare @2ndweek as date
declare @3rdweek as date
declare @4thweek as date
declare @5thweek as date
set @currentweek= dateadd(dd,0,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @1stweek= dateadd(dd,7,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @2ndweek= dateadd(dd,14,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @3rdweek= dateadd(dd,21,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @4thweek= dateadd(dd,28,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @5thweek= dateadd(dd,35,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
select
case
when ([1] >= @currentweek and [1]<@1stweek)and ([2] >= @currentweek and [2]<@1stweek) and ([3] >= @currentweek and [3]<@1stweek)then 'RAP'
when ([2] >= @currentweek and [2]<@1stweek)and ([3] >= @currentweek and [3]<@1stweek) then 'RA'
when ([1] >= @currentweek and [1]<@1stweek)and ([3] >= @currentweek and [3]<@1stweek) then 'AP'
when [3] >= @currentweek and [3]<@1stweek then 'A'
when ([1] >= @currentweek and [1]<@1stweek)and ([2] >= @currentweek and [2]<@1stweek) then 'RP'
when [2] >= @currentweek and [2]<@1stweek then 'R'
when [1] >= @currentweek and [1]<@1stweek then 'P'
else null
end as [current week]
,case
when ([1] >= @1stweek and [1]<@2ndweek)and ([2] >= @1stweek and [2]<@2ndweek) and ([3] >= @1stweek and [3]<@2ndweek)then 'RAP'
when ([2] >= @1stweek and [2]<@2ndweek)and ([3] >= @1stweek and [3]<@2ndweek) then 'RA'
when ([1] >= @1stweek and [1]<@2ndweek)and ([3] >= @1stweek and [3]<@2ndweek) then 'AP'
when [3] >= @1stweek and [3]<@2ndweek then 'A'
when (([1] >= @1stweek and [1]<@2ndweek)and ([2] >= @1stweek and [2]<@2ndweek)) then 'RP'
when [2] >=@1stweek and [2] < @2ndweek then 'R'
when [1] >=@1stweek and [1] < @2ndweek then 'P'
else null
end as [Next week Week]
I have tried to research the CREATE Function
command:
I came up with
CREATE FUNCTION testudf()
returns table
as
begin
Inserted the code
End
Go
However that did not work. Any ideas?
EDIT: Added the word table next to returns.
Msg 102, Level 15, State 31, Procedure RollingDateRAP, Line 81
Incorrect syntax near 'BEGIN'.
Ah try this:
CREATE FUNCTION testudf()
returns @tblOut TABLE (
[current week] varchar(31)
,[Next week Week] varchar(31)
)
as
begin
declare @currentweek as date
declare @1stweek as date
declare @2ndweek as date
declare @3rdweek as date
declare @4thweek as date
declare @5thweek as date
set @currentweek= dateadd(dd,0,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @1stweek= dateadd(dd,7,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @2ndweek= dateadd(dd,14,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @3rdweek= dateadd(dd,21,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @4thweek= dateadd(dd,28,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
set @5thweek= dateadd(dd,35,(dateadd(DD,1-datepart(DW,GETDATE()),getdate())))
INSERT INTO @tblOut
select
case
when ([1] >= @currentweek and [1]<@1stweek)and ([2] >= @currentweek and [2]<@1stweek) and ([3] >= @currentweek and [3]<@1stweek)then 'RAP'
when ([2] >= @currentweek and [2]<@1stweek)and ([3] >= @currentweek and [3]<@1stweek) then 'RA'
when ([1] >= @currentweek and [1]<@1stweek)and ([3] >= @currentweek and [3]<@1stweek) then 'AP'
when [3] >= @currentweek and [3]<@1stweek then 'A'
when ([1] >= @currentweek and [1]<@1stweek)and ([2] >= @currentweek and [2]<@1stweek) then 'RP'
when [2] >= @currentweek and [2]<@1stweek then 'R'
when [1] >= @currentweek and [1]<@1stweek then 'P'
else null
end as [current week]
,case
when ([1] >= @1stweek and [1]<@2ndweek)and ([2] >= @1stweek and [2]<@2ndweek) and ([3] >= @1stweek and [3]<@2ndweek)then 'RAP'
when ([2] >= @1stweek and [2]<@2ndweek)and ([3] >= @1stweek and [3]<@2ndweek) then 'RA'
when ([1] >= @1stweek and [1]<@2ndweek)and ([3] >= @1stweek and [3]<@2ndweek) then 'AP'
when [3] >= @1stweek and [3]<@2ndweek then 'A'
when (([1] >= @1stweek and [1]<@2ndweek)and ([2] >= @1stweek and [2]<@2ndweek)) then 'RP'
when [2] >=@1stweek and [2] < @2ndweek then 'R'
when [1] >=@1stweek and [1] < @2ndweek then 'P'
else null
end as [Next week Week];
RETURN;
End
Go