October 29, 2008 at 10:21 am
Hello I have this function that creates fiscal week:
create function [dbo].[FiscalWeek] (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @firstWeek datetime
declare @weekNum int
declare @year int
set @year = datepart(year, @myDate)+1
--Get 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
--Retreat to beginning of week
set @firstWeek = dateadd(day, (-datepart(dw, @firstWeek)), @firstWeek)
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+2)
return @weekNum
end
What can i do to it to return fiscal month?
October 29, 2008 at 10:26 am
Got to ask, what is the fiscal year? Also, what do you consider a fiscal month?
October 29, 2008 at 10:56 am
Hiya
Please look at the attached spreadsheet, for further details and how the fiscal week is based on.
October 29, 2008 at 12:24 pm
So, to paraphrase your spreadseet, Fiscal Year 2009 starts the first day after the last Saturday in February (2/24/2008) and ends the on the last Saturday of February in the following year. Correct?
October 30, 2008 at 3:18 am
Thats right....
October 31, 2008 at 12:21 pm
It appears your fiscal year is NOT a consistent number of days, even accounting for leap years. In the case of FY2009, the fiscal year is 371 days long. For FY 2010, it's just 364 days, which can mean a full 1 week difference in fiscal year length. Therefore, you need to specify the algorithm that determines which fiscal months get the extra weeks, or provide some means of knowing what rule to apply to decide where to end a given fiscal month. It seems far more likely that the pattern of 4 weeks vs. 5 weeks for each fiscal month will change from fy to fy, so you need to set out what rule to follow. Any attempt at a solution without such a rule is just a guess.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2008 at 12:28 pm
I have gotten side tracked by work and other issues. From my analysis, it appears the each fiscal quarter follows the following pattern 4 weeks, 5 weeks, 4 weeks. the exception being the final fiscal month (FM12) which is either 4 or 5 weeks depending on when the last Saturday of February falls.
October 31, 2008 at 2:27 pm
Ok, here's some code that will help. I take any given input date, determine it's fiscal year, and then generate a table of start and end dates for the fiscal months. The end dates are going to appear to be one day off, but with SQL, it's always best to compare a date against the two range values by having it be >= to the start of the range and < the end of the range, and as DateTime fields have a time component, this being off 1 day allows for accurate comparisons using that method against DateTime values that HAVE time components.
Here's the code:
DECLARE @fiscal_year AS smallint, @fy_start AS DateTime, @fy_end AS DateTime, @end_feb_start AS DateTime
DECLARE @end_feb_end AS DateTime, @get_gd_end_feb AS DateTime, @GIVEN_DATE AS DateTime, @num_weeks AS int
DECLARE @fy AS char(4)
SET @GIVEN_DATE = '02/27/2008'
PRINT 'GIVEN DATE is: ' + CAST(@GIVEN_DATE AS varchar(30))
SET @get_gd_end_feb = DATEADD(d,-1,DATEADD(d,0,'03/01/' + CAST(YEAR(@GIVEN_DATE) AS char(4))))
SET @get_gd_end_feb = DATEADD(d,1 - DATEPART(dw,@get_gd_end_feb),@get_gd_end_feb)
SET @fiscal_year =
CASE
WHEN @GIVEN_DATE > @get_gd_end_feb THEN YEAR(@get_gd_end_feb) + 1
ELSE YEAR(@GIVEN_DATE)
END
SET @fy = CAST(@fiscal_year AS char(4))
SET @end_feb_start = DATEADD(d,-1,DATEADD(d,0,'03/01/' + CAST(@fiscal_year - 1 AS char(4))))
SET @end_feb_end = DATEADD(d,-1,DATEADD(d,0,'03/01/' + CAST(@fiscal_year AS char(4))))
SET @fy_start = DATEADD(d,1 - DATEPART(dw,@end_feb_start),@end_feb_start)
SET @fy_end = DATEADD(d,1 - (DATEPART(dw,@end_feb_end) % 7),@end_feb_end)
PRINT 'FISCAL YEAR ' + @fy + ' includes dates >= ' + CAST(@fy_start AS varchar(30)) + ' and < ' +
CAST(@fy_end AS varchar(30))
SET @num_weeks = DATEDIFF(d,@fy_start,@fy_end) / 7
PRINT 'The number of weeks in fiscal ' + @fy + ' is ' + CAST(@num_weeks as varchar(2))
DECLARE @MONTHS TABLE (
FM tinyint PRIMARY KEY CLUSTERED,
NUM_WEEKS tinyint
)
INSERT INTO @MONTHS
SELECT 1,4 UNION ALL
SELECT 2,5 UNION ALL
SELECT 3,4 UNION ALL
SELECT 4,4 UNION ALL
SELECT 5,5 UNION ALL
SELECT 6,4 UNION ALL
SELECT 7,4 UNION ALL
SELECT 8,5 UNION ALL
SELECT 9,4 UNION ALL
SELECT 10,4 UNION ALL
SELECT 11,5 UNION ALL
SELECT 12, CASE WHEN @num_weeks = 52 THEN 4 ELSE 5 END
DECLARE @FISCAL_MONTHS TABLE (
FISCAL_YEAR smallint,
FISCAL_MONTH tinyint,
FM_NAME AS CAST(CAST(FISCAL_YEAR AS char(4)) + '_FM' + RIGHT('0' + CAST(FISCAL_MONTH AS varchar(2)),2) AS char(10)),
FISCAL_MONTH_START DateTime,
FISCAL_MONTH_END DateTime
)
INSERT INTO @FISCAL_MONTHS(FISCAL_YEAR, FISCAL_MONTH, FISCAL_MONTH_START)
SELECT @fiscal_year AS FISCAL_YEAR, M.FM AS FISCAL_MONTH,
DATEADD(wk,ISNULL((
SELECT SUM(M2.NUM_WEEKS)
FROM @MONTHS AS M2
WHERE M2.FM <= M.FM - 1),0),@fy_start
) AS FISCAL_MONTH_START
FROM @MONTHS AS M
UPDATE F
SET FISCAL_MONTH_END = DATEADD(wk,M.NUM_WEEKS,FISCAL_MONTH_START)
FROM @FISCAL_MONTHS AS F INNER JOIN @MONTHS AS M
ON F.FISCAL_MONTH = M.FM
SELECT *
FROM @FISCAL_MONTHS AS F INNER JOIN @MONTHS AS M
ON F.FISCAL_MONTH = M.FM
You'll have to adapt this based on how you intend to use it.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply