Have issue on fiscal week

Question

Hi all, I am having below codes, but it was showing incorrectly for the year when previous year is having 53 weeks.&nbsp;&nbsp;Scenario: I am computing Fiscal Week, where ISO Week starts in July, first Monday in July with complete 7 days will be Week 1, then must have 7 days every week.&nbsp;&nbsp;Could you help me to solve the error part here? (Period 2020-07-01 till 2021-07-04)Expected outcome: 2020-07-01 till 2020-07-5 should be 202053 and the weeks after should be one week earlier.&nbsp;Code:&nbsp;CONCAT(&nbsp; &nbsp; CASE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHEN RIGHT('0' +&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CAST(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &lt; 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) &lt;&gt; 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS VARCHAR(2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , 2) = '00' THEN YEAR(DATEADD(MONTH, 6, [DATE])) - 1 -- Subtract 1 from the year if FY_WEEK ends with "00"&nbsp; &nbsp; &nbsp; &nbsp; ELSE YEAR(DATEADD(MONTH, 6, [DATE]))&nbsp; &nbsp; END,&nbsp; &nbsp; RIGHT('0' +&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &lt; 7 AND DATEPART(ISO_WEEK, [DATE]) = 53 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) &lt;= 7 THEN '01' -- Week 52 or 53WHEN MONTH([DATE]) &lt; 7 AND DATEPART(ISO_WEEK, [DATE]) = 52 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) &lt;= 7 THEN '01' -- Week 52 or 53&nbsp;WHEN MONTH([DATE]) = 7 AND DATEPART(ISO_WEEK, [DATE]) = 27&nbsp; AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) &lt;= 7 THEN '01'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) - 26 = 53 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) &lt;= 7 THEN '01' -- Week 52 or 53 in new FYWHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) - 26 = 52 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) &lt;= 7 THEN '01' -- Week 52 or 53 in new FY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN RIGHT('0' +&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CAST(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &lt; 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) &lt;&gt; 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS VARCHAR(2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , 2) = '00' THEN '52' -- Set to Week 52 if the calculated value ends with "00"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE RIGHT('0' +&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CAST(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &lt; 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN MONTH([DATE]) &gt;= 7 AND DATEPART(ISO_WEEK, [DATE]) &lt;&gt; 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE DATEPART(ISO_WEEK, [DATE]) - 26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS VARCHAR(2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , 2)&nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; , 2)) AS FY_WEEK,

olafhelper · Answer

Instead doing complex "calculation" on the week use better a calendar table for mapping date to fiscal week, see https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Forum Discussion

Have issue on fiscal week

Share

Resources