This post is a quick and dirty solution to a question that came up on Twitter on the
#sqlhelp hash tag from Barnaby Self (
Blog|
Twitter). @B4PJS wrote up a related blog post
here. Please note that this possible solution has some potentially significant limitations: First, the possible solution assumes that the Fiscal Year begins on same date every year (some companies like Cisco have 52 week Fiscal Years). Second, the solution assumes that the Fiscal Year start date is Day 1 of Week 1 regardless of the day of the week. In other words, this solution is NOT affected by the SET DATEFIRST command. If SET DATEFIRST needs to impact Fiscal Week determination, this solution will need to be modified. Third, using scalar-valued UDFs (User Defined Functions) can cause significant performance problems if used in WHERE or GROUP BY clauses (nod to
@DaveBally) - alternate solutions may yield better results. A possible better performing solution might be using the UDF described below in conjunction with either a persisted computed column or an indexed view.
-- Developed and tested only on SQL Server 2008 Dev Ed.
-- Assumes Fiscal Year begins July 1st.
-- Assumes day 1 of week 1 is July 1st regardless of day of week.
CREATE FUNCTION dbo.udf_GetFiscalYearWeek(@InputDate date)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @FiscalYear int;
-- Following logic will need to change if Fiscal Year start date different from YYYY0701.
IF (MONTH(@InputDate) >= 7)
BEGIN
SET @FiscalYear = YEAR(@InputDate) + 1;
END
ELSE
BEGIN
SET @FiscalYear = YEAR(@InputDate);
END
DECLARE @FiscalYearStartDate date;
-- Change '0701' as required.
SET @FiscalYearStartDate = CAST((CAST((@FiscalYear - 1) AS varchar(4)) + '0701') AS DATE);
DECLARE @FiscalYearWeek int;
SET @FiscalYearWeek = DATEDIFF(d, @FiscalYearStartDate, @InputDate) / 7 + 1;
SET @Result = @FiscalYearWeek;
RETURN @Result
END
GO
Thanks for reading. Any feedback you have will be appreciated.