Fiscal Week
-
Thank you very much everyone. I am learning every day from this forum.
I am looking for help as how to calculate fiscal week number from calendar date. All I have is calendar date and this is how I create fiscal year from calendar year.
set FiscalYear =
case when DatePart(mm, transaction_date) >= 4 then datepart(yyyy, transaction_date) +1
else datepart(yyyy, transaction_date) end
Our fiscal year starts April 1 and ends March 31. So this is fiscal year 2005 which started April 1, 2004 and will end March 31, 2005.
I cannot use function working SQL 7.
Thank you very much in advance.
M.
-
-
I'm not quite sure whether you're asking how to calculate the fiscal week or year. Regarding your supplied SQL code, if DATEPART isn't working, try using MONTH(transaction_date) to get your month value, and YEAR(transaction_date) for the year.
Re: calculating a fiscal week...does your fiscal year always start on 4/1, or is 4/1 a "target" date that is adjusted forwards or backwards to get to the "start" (Sun, Mon, etc.) of your company's week (I used to work for a company that adjusted their fiscal date)? Regardless, once you have your fiscal start date, getting the fiscal week should be easy.
-
Thank you to all who have replied.
Chuck how I calculate fiscal year works well. I do not know how to calculate fiscal week.
Our fiscal year always starts on April 1 regardless which day it is.
If you could help me understand how fiscal start date helps create fiscal week I would be very grateful.
Thank you
M.
-
I usually create a "Dates" table to store such information. I can then populate the fiscal calendar easily. I start with 34700 because that's the Excel numerical equivalent of 1/1/1995. I add the Date_Key column to tables where appropriate. You can add a "Holiday" column to make it more useful. This code is old, but does the job.
I imagine a stored procedure or UDF could accomplish the same thing.
CREATE TABLE dbo.Dates (
Date_Key [int] IDENTITY (34700, 1) NOT NULL ,
SQL_Date [smalldatetime] NOT NULL ,
Integer_Date [int] NOT NULL ,
Date_Week [tinyint] NOT NULL ,
Date_Month [tinyint] NOT NULL ,
Date_Quarter [tinyint] NOT NULL ,
Date_Year [smallint] NOT NULL ,
Fiscal_Week [tinyint] NOT NULL ,
Fiscal_Quarter [tinyint] NOT NULL ,
Fiscal_Year [smallint] NOT NULL ,
Month_Name_Short [char] (3) NOT NULL ,
Month_Name_Long [varchar] (9) NOT NULL
)
ALTER TABLE [dbo].[Dates] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [Date_Key]) ON [PRIMARY]
CREATE INDEX [IX_SQL_Date] ON [dbo].[Dates]([SQL_Date]) ON [PRIMARY]
CREATE INDEX [IX_Integer_Date] ON [dbo].[Dates]([Integer_Date]) ON [PRIMARY]
GRANT SELECT ON [dbo].[Dates] TO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
SET NOCOUNT ON
declare @insertdate as smalldatetime
declare @dateweek as tinyint
declare @datemonth as tinyint
declare @datequarter as tinyint
declare @dateyear as smallint
declare @fiscalweek as tinyint
declare @fiscalquarter as tinyint
declare @fiscalyear as smallint
declare @monthnameshort as char(3)
declare @monthname as varchar(9)
declare @integerdate as integer
set @insertdate = '1995-01-01'
WHILE (@insertdate < '2010-01-01')
BEGIN
set @dateweek = datepart(week, @insertdate)
set @datemonth = month(@insertdate)
set @datequarter = datename(quarter, @insertdate)
set @dateyear = year(@insertdate)
set @integerdate = cast(convert(char(8),@insertdate,112) as int)
if @datequarter = 4
BEGIN
set @fiscalquarter = 1
set @fiscalyear = @dateyear + 1
set @fiscalweek = @dateweek - 39
END
else
BEGIN
set @fiscalquarter = @datequarter + 1
set @fiscalyear = @dateyear
set @fiscalweek = @dateweek + 13
END
set @monthname = datename(month, @insertdate)
set @monthnameshort = substring(@monthname, 1, 3)
insert into Dates (SQL_Date, Integer_Date, Date_Week, Date_Month, Date_Quarter, Date_Year, Fiscal_Week, Fiscal_Quarter, Fiscal_Year, Month_Name_Short, Month_Name_Long) values (@insertdate, @integerdate, @dateweek, @datemonth, @datequarter, @dateyear, @fiscalweek, @fiscalquarter, @fiscalyear, @monthnameshort, @monthname)
set @insertdate = dateadd(day, 1, @insertdate)
END
Don
http://www.biadvantage.com -
Assuming that 'transaction_date' in your example is the date in which you need to find the fiscal week of, its a matter of determining the difference between 4/1 and that date. You can use DATEDIFF(day,<startdate>,<enddate>
to find the difference, then divide by 7. You may, however, need to do some additional interrogation for partial weeks (depending on what your business rules are for this process), but it you should be able to do all of this mathematically using SQL date functions.Hope this helps.
Good luck!
-
Here's a way to get the Fiscal Week, using the DatePart function.
However, the devil is in the details as they say, so I've included
some specification clarification....
1. Specification by example:
Date: 4/1/2004
Fiscal Year Start: 4/1/2004
Fiscal Year End: 3/31/2005
Fiscal Year: 2005
Calendar week of 4/1/2004: 14
Fiscal week of 4/1/2004: 1
Deduced rule: Fiscal Week = Calendar Week - 14 + 1
where 14 is the "base calendar week" for this fiscal year.
Date: 3/31/2005
Fiscal Year Start: 4/1/2004
Fiscal Year End: 3/31/2005
Fiscal Year: 2005
Calendar week of 3/31/2005: 14
Fiscal week of 3/31/2005: 53
Deduced rule: Fiscal Week = Calendar Week - 14 + 1 + 52
2. Calendar example: This is the April 2004 Calendar, assuming each fiscal week
starts on Sunday, and the first fiscal week is a partial week:
S. M. T. W. TH F. S. | FISCAL WK
xx xx xx xx 01 02 03 | 001
04 05 06 07 08 09 10 | 002
11 12 13 14 15 16 17 | 003
18 19 20 21 22 23 24 | 004
25 26 27 28 29 30 xx | 005
3. Here is SQL that should implement the specification above. If your first fiscal week is 000
rather than 001, or if the beginning of the fiscal week is not Sunday, then you'll have to
make some adjustments!
SET @FiscalYear = CASE
WHEN DATEPART('mm', @transaction_date) >= 4
THEN DATEPART('yyyy', @transaction_date) +1
ELSE DATEPART('yyyy', @transaction_date)
END
SET @FYBase = @FiscalYear - 1
SET @FYStart = CAST( ('4/1/' + @FYBase) AS smalldatetime)
SET @BaseCalWeek = DATEPART('ww',@FYStart)
SET @TransCalWeek = DATEPART('ww',@transaction_date)
SET @FiscalWeek = CASE
WHEN DATEPART('yyyy', @transaction_date) = @FiscalYear
THEN @TransCalWeek - @BaseCalWeek + 1 + 52
ELSE @TransCalWeek - @BaseCalWeek + 1
END
4. Also, I'd test how the calculation works for Dec 31 2004 and Jan 1 2005
to verify the addition of 52 weeks when rolling over the calendar year boundary...
it might turn out that you should add +51 weeks, or +53, though I doubt it.
Regards,Bob Monahon
-
Thank you everyone for your help.
THANK YOU Bob Monahon. It works like a charm. I appreciate very much the time you have taken to help me.
M.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply