Get Fiscal Week Number

Question

Get Fiscal Week Number

mm7861

Ten Centuries

Points: 1388
More actions
April 29, 2015 at 5:39 am

#317807

Hi all,
Our fiscal year starts on 2015-04-06, and I need to assign a week number that starts from this date. Any idea how I can achieve this?
This piece of code gets close, but only works for the fiscal month starting on 2015-04-01
SELECT DATEPART (WEEK,DATEADD(MONTH,-3,GETDATE()))
Any help will be much appreciated!

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mm7861 (4/29/2015)
Hi all,
Our fiscal year starts on 2015-04-06, and I need to assign a week number that starts from this date. Any idea how I can achieve this?
This piece of code gets close, but only works for the fiscal month starting on 2015-04-01
SELECT DATEPART (WEEK,DATEADD(MONTH,-3,GETDATE()))
Any help will be much appreciated!

You should use a calendar table. It will save you tons of grief. http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]

http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mm7861 Ten Centuries Points: 1388 More actions · Answer 2

I am using a calendar table. The code is intended for a stored procedure that will populate the calendar table.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

mm7861 (4/29/2015)
I am using a calendar table. The code is intended for a stored procedure that will populate the calendar table.

No there are two links I posted. The first one explains how to use it, the second one is an example of filling it up.

From your original post it doesn't at all look like you are using a calendar table because there is nothing there but getdate().

If you had a calendar table you would use datediff with the basis being the fiscal year start.

The query would be something like this:

declare @FiscalYearStart datetime = '2015-04-06' --this would be in your calendar table.

select DATEDIFF(week, @FiscalYearStart, getdate())

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mm7861 Ten Centuries Points: 1388 More actions · Answer 4

Apologies for being vague!

The only issue I'm having with that datediff code is that I need the fiscal date to change every year. The calendar table will have 100 years worth of dates so it'll need to calculate the Fiscal Week for each year, if that makes sense?

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

mm7861 (4/29/2015)
Apologies for being vague!
The only issue I'm having with that datediff code is that I need the fiscal date to change every year. The calendar table will have 100 years worth of dates so it'll need to calculate the Fiscal Week for each year, if that makes sense?

What are the rules for the first week of every fiscal year? Would it simply be the first full week of April or the first week of April that has a Monday in it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

Well is your fiscal year going to start on 4/6 every year or will that date change every year?

Weeks don't break into years cleanly the way months do. So it's relatively important that it's clearly defined how you plan to break that out.

mm7861 Ten Centuries Points: 1388 More actions · Answer 7

The Fiscal Year will always start on 6th April. I've managed to create the Fiscal Start Date based on the DateKey in the calendar table using this code;

SELECT DateKey, CASE WHEN RIGHT(REPLACE(DateKey,'-',''),4) < 0406 THEN CAST(DATEADD(YEAR, -1,DateKey) AS NVARCHAR(4)) + '-04-06' ELSE CAST(YEAR(DateKey) AS NVARCHAR(4)) + '-04-06' END

FROM Calendar

So, I tried to replace @FiscalYearStart in the code below with the code above, and it was giving me issues around the time where the new Fiscal Year starts (giving me week 0)

select DATEDIFF(week, @FiscalYearStart, getdate())

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

mm7861 (4/29/2015)
The Fiscal Year will always start on 6th April. I've managed to create the Fiscal Start Date based on the DateKey in the calendar table using this code;
SELECT DateKey, CASE WHEN RIGHT(REPLACE(DateKey,'-',''),4) < 0406 THEN CAST(DATEADD(YEAR, -1,DateKey) AS NVARCHAR(4)) + '-04-06' ELSE CAST(YEAR(DateKey) AS NVARCHAR(4)) + '-04-06' END
FROM Calendar
So, I tried to replace @FiscalYearStart in the code below with the code above, and it was giving me issues around the time where the new Fiscal Year starts (giving me week 0)
select DATEDIFF(week, @FiscalYearStart, getdate())

It would help if you shared your calendar table implementation. Do you really always start on April 6th? Even if that date is on the weekend?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mm7861 Ten Centuries Points: 1388 More actions · Answer 9

The calendar table has a datekey in the format of yyyy-mm-dd. There are then columns that display the date in different formats, years, months, quarters etc.

I asked the question about the Fiscal Year start date, and that's what I was told! (I was surprised too). The code I posted above managed to give me a 'FiscalYearStartDate' for each row in the calendar table, but I'm struggling to find a way to use these two dates and get a consistent week number. It seems to be falling over when a new fiscal year starts, where it either gives it a week 0, or week 1 that only lasts until a sunday!

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 10

Is this something like what you were looking for?

WITH CTE_TALLY_10(COL_ONE) AS(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

CTE_TALLY_100(COL_ONE) AS(

SELECT a.COL_ONE FROM CTE_TALLY_10 a, CTE_TALLY_10 b

),

CTE_TALLY(COL_ONE) AS(

SELECT ROW_NUMBER() OVER(PARTITION BY a.COL_ONE ORDER BY a.COL_ONE) - 1 FROM CTE_TALLY_100 a, CTE_TALLY_100 b

)

SELECT TALLY_ONE.COL_ONE AS YEAR_DAY, DATEADD(day, TALLY_ONE.COL_ONE, DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015')) AS [DATE], (DATEDIFF(day, DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015'),DATEADD(day, TALLY_ONE.COL_ONE, DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015'))) / 7) + 1 AS FISCAL_WEEK,

DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015') AS YEAR_DATE

FROM CTE_TALLY TALLY_ONE, CTE_TALLY TALLY_TWO

WHERE

TALLY_ONE.COL_ONE < 1000

AND TALLY_TWO.COL_ONE < 100

AND DATEADD(year, 1, DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015')) > DATEADD(day, TALLY_ONE.COL_ONE, DATEADD(year, TALLY_TWO.COL_ONE, '4/6/2015'))

ORDER BY [DATE] ASC

Michael Meierruth SSChampion Points: 10051 More actions · Answer 11

Seeing this weird fiscal calendar really intrigues me - never seen or heard of this one.

I wonder how many different fiscal calendars there are out there in the real world.

I wonder what fiscal calendars the Chinese use or - the Russians - the Iranians?

It's a never ending story...

BTW, why does the year_day start with zero? It doesn't feel right... 🙂

mm7861 Ten Centuries Points: 1388 More actions · Answer 12

That CTE seems to do the job! Thank you very much!

mm7861 Ten Centuries Points: 1388 More actions · Answer 13

I actually managed to crack my piece of code too to get the same results as that CTE;

select datekey, CASE WHEN RIGHT(replace(datekey,'-',''),4) < 0406 then CAST(dateadd(year, -1,DateKey) AS nvarchar(4)) + '-04-06' else CAST(year(DateKey) AS nvarchar(4)) + '-04-06' end,

(DATEDIFF(day,(CASE WHEN RIGHT(replace(datekey,'-',''),4) < 0406 then CAST(dateadd(year, -1,DateKey) AS nvarchar(4)) + '-04-06' else CAST(year(DateKey) AS nvarchar(4)) + '-04-06' end),datekey) /7) + 1

from Calendar

Success!! Thank you everyone for your help 🙂

mm7861 Ten Centuries Points: 1388 More actions · Answer 14

I will be doing it more than once. The calendar table will need to be updated once the public holidays have been added to the upcoming years. At the moment it only goes to 2016.