DerekRoberts
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Cheers Angelll
I fear the syntax spectre will rear its ugly head again before long..can't wait
:)