return Fiscal Year based on YYYY-week format

Question

return Fiscal Year based on YYYY-week format

natividad.castro

Old Hand

Points: 364
More actions
November 6, 2015 at 10:57 am

#304987

Hi all,
I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the YearWeek is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.
For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.
Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.
select sum(hours) as total, yearweek from tablename group by yearweek
Then I loop through using C#.
I can return the FY using an actual date, but not sure how to do it for year-week format for any given year.
select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY
By the way, the Fiscal Year starts on October First and end on September 30.
Any help is greatly appreciated.
Thanks,
Nato

Viewing 12 posts - 1 through 11 (of 11 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

To simplify this, you could create a calendar table that will contain the fiscal year and week as two of its columns so you don't have to bother with problematic calculations that are prone to have exceptions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

natividad.castro Old Hand Points: 364 More actions · Answer 2

Thanks for the reply. I can't do changes to database. Change requests take very long for approval and implementation.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

natividad.castro (11/6/2015)
By the way, the Fiscal Year starts on October First and end on September 30.

When do weeks start? How do you define the first week of the year?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

natividad.castro Old Hand Points: 364 More actions · Answer 4

Week starts on sunday and on saturday.

Fiscal year starts October 1.

Thanks

natividad.castro Old Hand Points: 364 More actions · Answer 5

First week starts at week 40, which is week 1 of the fiscal year

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Which of this will define Week 1 for 2016?

2015-10-01 to 2015-10-02

2015-10-01 to 2015-10-03

2015-09-26 to 2015-10-02

2015-09-27 to 2015-10-03

2015-10-03 to 2015-10-09

2015-10-04 to 2015-10-10

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

natividad.castro Old Hand Points: 364 More actions · Answer 7

Thanks for your reply.

None of the dates will define week 1 for FY16

here are the definitions:

2015-10-01 to 2015-10-10 ---this will be week 1 for FY16, but it's recorded as 2015-41

2015-10-11 to 2015-10-17 ---this will be week 2 for FY16, but it's recorded as 2015-42

2015-10-18 to 2015-10-24---this will be week 3 for FY16, but it's recorded as 2015-43

2015-10-25 to 2015-10-31 ---this will be week 4 for FY16, but it's recorded as 2015-44

and so on....

Thanks

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 8

natividad.castro (11/6/2015)
Thanks for your reply.
None of the dates will define week 1 for FY16
here are the definitions:
2015-10-01 to 2015-10-10 ---this will be week 1 for FY16, but it's recorded as 2015-41
2015-10-11 to 2015-10-17 ---this will be week 2 for FY16, but it's recorded as 2015-42
2015-10-18 to 2015-10-24---this will be week 3 for FY16, but it's recorded as 2015-43
2015-10-25 to 2015-10-31 ---this will be week 4 for FY16, but it's recorded as 2015-44
and so on....
Thanks

Can you lay that out one more time but for Dec 2015, please? Thanks.

--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)

natividad.castro Old Hand Points: 364 More actions · Answer 9

2015-10-01 to 2015-10-10 ---this will be week 1 for FY16, but it's recorded as 2015-41

2015-10-11 to 2015-10-17 ---this will be week 2 for FY16, but it's recorded as 2015-42

2015-10-18 to 2015-10-24---this will be week 3 for FY16, but it's recorded as 2015-43

2015-10-25 to 2015-10-31 ---this will be week 4 for FY16, but it's recorded as 2015-44

The format is a calendar year, so it keeps increasing up to December, and then starts at week 1 for January

2015-12-20 to 2015-12-26 will be week 12 for FY16, and recorded as 2015-52

2015-12-27 to 2016-01-02 will be week 13 for FY16, and recorded as 2016-01

until it gets to September 30, which is week 40 for the calendar year, but week 52 for Fiscal Year

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

Here's a couple solutions (if I'm understanding this correctly).

The caveat here is that I'm understanding you problem, which I think I am.

If you can create an inline table valued function then a specialized calendar function may be the way to go. Something like this (not a complete calendar table function, modify as needed as this is a helpful tool to have):

CREATE FUNCTION dbo.fncalendar(@start date, @end date)

RETURNS TABLE AS RETURN

WITH X(R) AS (

SELECT 1

FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(R)), -- 45 rows

Dates(DateText) AS

(

SELECT TOP (DATEDIFF(DD,@start,@end))

DATEADD(DD,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@start)

FROM X a, X b, X c -- 91125 rows (45^3)

)

SELECT

DateText,

CalYear = YEAR(DateText),

FiscalYear = CASE

WHEN MONTH(DateText)<10 THEN YEAR(DateText)

ELSE YEAR(DATEADD(YY,1,DateText))

END,

MonthOfYearNbr = MONTH(DateText),

MonthOfYearText = MONTH(DateText),

[DayOfMonth] = DATEPART(Day,DateText),

WeekOfYear = DATEPART(WEEK,DateText),

FiscalWeek = CASE

WHEN MONTH(DateText) = 10 AND DATEPART(Day,DateText)<8 THEN 41

ELSE DATEPART(WEEK,DateText)

END,

[DayOfWeek] = DATEPART(WEEKDAY,DateText)

FROM Dates;

Then you could could do this:

-- Sample data

DECLARE @sometable TABLE (YearWeek varchar(8), TotalHours int);

INSERT @sometable VALUES('2015-10', 6),('2015-12',19),('2015-20',38),('2015-30',50),

('2015-38',6),('2015-40',19),('2015-45',38),('2015-54',37),('2016-10',15),('2016-12',22);

-- get everything from 10/1/2014 through 10/1/2014

DECLARE @start date = '20141001', @end date = '20161001';

-- Solution

SELECT FiscalYear, HoursForYear = SUM(TotalHours)

FROM @sometable s

CROSS APPLY dbo.fncalendar('20150101','20170101')

WHERE CalYear = SUBSTRING(YearWeek,1,4)

AND FiscalWeek = SUBSTRING(YearWeek,6,2)

GROUP BY FiscalYear;

If new functions are not allowed then you could take the above logic and do this:

-- Sample data

DECLARE @sometable TABLE (YearWeek varchar(8), TotalHours int);

INSERT @sometable VALUES('2015-10', 6),('2015-12',19),('2015-20',38),('2015-30',50),

('2015-38',6),('2015-40',19),('2015-45',38),('2015-54',37),('2016-10',15),('2016-12',22);

-- get everything from 10/1/2014 through 10/1/2014

DECLARE @start date = '20141001', @end date = '20161001';

-- Solution

WITH X(R) AS (

SELECT 1

FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),

(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(R)), -- 45 rows

Dates(DateText) AS

(

SELECT TOP (DATEDIFF(DD,@start,@end))

DATEADD(DD,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@start)

FROM X a, X b, X c -- 91125 rows (45^3)

),

Calendar AS

(

SELECT

CalYear = YEAR(DateText),

FiscalYear = CASE

WHEN MONTH(DateText)<10 THEN YEAR(DateText)

ELSE YEAR(DATEADD(YY,1,DateText))

END,

WeekOfYear = DATEPART(WEEK,DateText),

FiscalWeek = CASE

WHEN MONTH(DateText) = 10 AND DATEPART(Day,DateText)<8 THEN 41

ELSE DATEPART(WEEK,DateText)

END

FROM Dates

)

SELECT FiscalYear, HoursForYear = SUM(TotalHours)

FROM @sometable s

CROSS APPLY Calendar

WHERE CalYear = SUBSTRING(YearWeek,1,4)

AND FiscalWeek = SUBSTRING(YearWeek,6,2)

GROUP BY FiscalYear;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

natividad.castro Old Hand Points: 364 More actions · Answer 11

Thank you very much for your response.

The table that I'm working with doesn't have dates.

I tried your example "as is",

I created the function and then run the code below

[/center]

-- Sample data

DECLARE @sometable TABLE (YearWeek varchar(8), TotalHours int);

INSERT @sometable VALUES('2015-10', 6),('2015-12',19),('2015-20',38),('2015-30',50),

('2015-38',6),('2015-40',19),('2015-45',38),('2015-54',37),('2016-10',15),('2016-12',22);

-- get everything from 10/1/2014 through 10/1/2014

DECLARE @start date = '20141001', @end date = '20161001'; --------the table doesn't dates...

-- Solution

SELECT FiscalYear, HoursForYear = SUM(TotalHours)

FROM @sometable s

CROSS APPLY dbo.fncalendar('20150101','20170101')

WHERE CalYear = SUBSTRING(YearWeek,1,4)

AND FiscalWeek = SUBSTRING(YearWeek,6,2)

GROUP BY FiscalYear;

and returns total hours for 2015 is 909 and 2016 is 525. The total hours from your example is only 250.

Thanks