Without digging into your custom SQL (sorry), check out my article called SQL Calendar Table, which is code that will create a table of days and all sorts of other column such as custom weeks and months such that you can modify your query to join on this table, and sum numbers based on week number.

This would save you the headache of having to custom code it in every SP, SSIS package, or SSRS report that requires this logic.

My week begins on a Sunday. Although I do not know how to code for it myself I believe in the case of the week spanning January I could perhaps use a function to determine the number value in a week and subtract that from the value to have the week end 12/31?