[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Scarlar Value Function Syntax error (probably)

Righto Experts

Pigeon steps.....Am trying to set myself up my first written Scarlar Function and I keep getting error messeges.

The current one is :
Msg 102, Level 15, State 1, Procedure FiscalWk, Line 48
Incorrect syntax near '@FsWks'.

The script is meant to work out Fiscal week and year utilising a set date (defined from the given @startMonth variable)

Now I'm sure there is a more efficient way of processing this information (pointers are always greatly recived :) ) but I am seeking help in just getting this function to work please.

Thanks in advance


Create function [dbo].[FiscalWk] (@startMonth varchar(2), @myDate datetime) 
returns int 
as  
begin  
declare @dy1 datetime  
declare @dynm int 
declare @year int
declare @dtadj int 
declare @Fsyr int
declare @FsWks int
declare @mnth int
 
--Gets mnth from declared Mydate
Set @mnth = datepart(month,@mydate)
--Gets yr from declared mydate
Set @year = datepart(year, @myDate)
--Works out Fiscal Year
Set @Fsyr = Case 
when @mnth = 1 then @year-1 
else @year
End
--gets 1st date of fiscal year from mydate and start month from delcaration in controlling table (MainCli)
Set @dy1 = convert(datetime,@year + @startMonth + '01',103) 
-- gets day number of 1st day of year ie 1 = Monday 
Set @dynm = datepart(dw,@dy1)
-- works out integer to as Monday = 1st day of fiscal year then that would be correct, but if it was a Tuesday then that would be 6 days after 
--the 1st week of a year
 
Set @dtadj = Case 
When @dynm = 1 then 0
When @dynm = 2 then 6
When @dynm = 3 then 5
When @dynm = 4 then 4
When @dynm = 5 then 3
When @dynm = 6 then 2
When @dynm = 7 then 1
End
 
Set @FsWks = (Round((((Datediff(day,@dy1,@mydate)-dtadj)/7)+2),2))+@Fsyr
return @FsWks

Open in new window

0
DerekRoberts
Asked:
DerekRoberts
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the END and a @ (before dtadj in the last expression) were missing
Create function [dbo].[FiscalWk] (@startMonth varchar(2), @myDate datetime) 
returns int 
as  
begin  
declare @dy1 datetime  
declare @dynm int 
declare @year int
declare @dtadj int 
declare @Fsyr int
declare @FsWks int
declare @mnth int
 
--Gets mnth from declared Mydate
Set @mnth = datepart(month,@mydate)
--Gets yr from declared mydate
Set @year = datepart(year, @myDate)
--Works out Fiscal Year
Set @Fsyr = Case 
when @mnth = 1 then @year-1 
else @year
End
--gets 1st date of fiscal year from mydate and start month from delcaration in controlling table (MainCli)
Set @dy1 = convert(datetime,@year + @startMonth + '01',103) 
-- gets day number of 1st day of year ie 1 = Monday 
Set @dynm = datepart(dw,@dy1)
-- works out integer to as Monday = 1st day of fiscal year then that would be correct, but if it was a Tuesday then that would be 6 days after 
--the 1st week of a year
 
Set @dtadj = Case 
When @dynm = 1 then 0
When @dynm = 2 then 6
When @dynm = 3 then 5
When @dynm = 4 then 4
When @dynm = 5 then 3
When @dynm = 6 then 2
When @dynm = 7 then 1
End
 
Set @FsWks = ( Round(((   ( Datediff(day,@dy1,@mydate) -@dtadj)/7)  +2),2))+@Fsyr
  return (@FsWks)
End

Open in new window

0
 
DerekRobertsAuthor Commented:
Fan-dabby-Dozie

Cheers Angelll

I fear the syntax spectre will rear its ugly head again before long..can't wait

:)

0
 
nmcdermaidCommented:
Now I'm sure there is a more efficient way of processing this information (pointers are always greatly recived :)
If you don't mind a pointer: If you are doing  lot of reporting then its a good idea to have a calendar table. This has a unique indexed key on date which has every date you could possibly report on, then it has a whole host of fields which slot the date into a particular grouping
CalendarDate
Financial Year
Financial Month
Financial Week
Calendar Year
Calendar Month
Calendar Week
Plus a bunch of boolean type fields:
IsPublicHoliday
IsWeekend
IsYTD
IsMTD
YTD and MTD type fields are update by a daily process.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now