Well theres a possible typo in your code for the DATEMNAME(wk,DateValue)=29 which is repeated twice.

I dont know if this is by design or not, but it seems odd that you classify week 29 as Week 3 and week 30 as week 5.

A Simpler solution would be to use this case statement

,CASE WHEN DATENAME(wk,DateValue)>=27 then DATENAME(wk,DateValue)-26

ELSE DATENAME(wk,DateValue)+27

end

It doesnt take into account any special logic like the WK 29 issue, and assumes every year follows the same pattern.

It also makes the script easier to understand and maintain.

As Robin suggested I would materialise this as a Calendar Table.