question

pzz avatar image
pzz asked

sql code to get week number for FiscalYear

Need sql code to get week number for FiscalYear – Date table ranges from 1975 to 2024 Already have weekno (WeekOfYear) for CalendarYear -week count start from 1st of April and end on 31st march -Week start from Sunday to Saturday -On Fiscalyear end, week also ended eg. 1st apr 2016 -Week 1 2nd apr 2016 - week 1 3rd apr 2016 to 9th apr 2016 -week 2 10th apr 2016 to 16th apr 2016 -week 3 ... and so on
sql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@pzz Week count beginning from April 1st does not make much sense because it looks like you are looking for a calendar with full weeks. Those calendars begin not on hard date but rather on "the first week of [whatever month]", in your case the month is April. There also has to be a rule regarding the first week of this year and the last week of the previous year for those occasions when the week begins in, say, March, and ends in April. Is this the first week of this year or last week of last year? The rule maybe "Wednesday wins" or "Thursday wins" depending on requirements. For example, suppose the "Wednesday wins" is used, so in 2014, where there is a week from March 30 (Sunday) till April 5 (Saturday), Wednesday comes in April, and therefore, fiscal year begins on Sunday, March 30 2014 and ends on Saturday, March 28 (52 full weeks). Similarly, next fiscal year begins on Sunday, March 29 and ends on Saturday, April 2, this year has extra week (#53) due to the fact that the last week's Wednesday is still in March (March 30). The next year then begins on Sunday, April 3 2016, etc... Please provide some details. You can let VS to generate such calendar for you (SSAS project).
1 Like 1 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.