Hi all, I am having below codes, but it was showing incorrectly for the year when previous year is having 53 weeks.
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.
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.
Code:
CONCAT(
CASE
WHEN RIGHT('0' +
CAST(
CASE
WHEN MONTH([DATE]) < 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) <> 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26
ELSE DATEPART(ISO_WEEK, [DATE]) - 26
END AS VARCHAR(2)
)
, 2) = '00' THEN YEAR(DATEADD(MONTH, 6, [DATE])) - 1 -- Subtract 1 from the year if FY_WEEK ends with "00"
ELSE YEAR(DATEADD(MONTH, 6, [DATE]))
END,
RIGHT('0' +
CASE
WHEN MONTH([DATE]) < 7 AND DATEPART(ISO_WEEK, [DATE]) = 53 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) <= 7 THEN '01' -- Week 52 or 53
WHEN MONTH([DATE]) < 7 AND DATEPART(ISO_WEEK, [DATE]) = 52 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) <= 7 THEN '01' -- Week 52 or 53
WHEN MONTH([DATE]) = 7 AND DATEPART(ISO_WEEK, [DATE]) = 27 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) <= 7 THEN '01'
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) - 26 = 53 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) <= 7 THEN '01' -- Week 52 or 53 in new FY
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) - 26 = 52 AND DATEPART(DAY, DATEADD(WEEK, 1, [DATE])) <= 7 THEN '01' -- Week 52 or 53 in new FY
WHEN RIGHT('0' +
CAST(
CASE
WHEN MONTH([DATE]) < 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) <> 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26
ELSE DATEPART(ISO_WEEK, [DATE]) - 26
END AS VARCHAR(2)
)
, 2) = '00' THEN '52' -- Set to Week 52 if the calculated value ends with "00"
ELSE RIGHT('0' +
CAST(
CASE
WHEN MONTH([DATE]) < 7 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) = 01 THEN DATEPART(ISO_WEEK, [DATE]) + 26
WHEN MONTH([DATE]) >= 7 AND DATEPART(ISO_WEEK, [DATE]) <> 01 THEN DATEPART(ISO_WEEK, [DATE]) - 26
ELSE DATEPART(ISO_WEEK, [DATE]) - 26
END AS VARCHAR(2)
)
, 2)
END
, 2)
) AS FY_WEEK,
