Link to home
Start Free TrialLog in
Avatar of DerekRoberts
DerekRobertsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DerekRoberts

ASKER

Fan-dabby-Dozie

Cheers Angelll

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

:)

Avatar of nmcdermaid
nmcdermaid

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.