Charlottecb (12/7/2016)
...From ParentTable a
left outer join (selecta.[RecordID],
isnull(min(A.[Number]), 0) as [MinNumber]
from[ChildTable] a
where a.[StartDate] between @Week02 and @Week03
group by a.[RecordID]) y on y.[RecordID] = a.[RecordID]
...
Do you have a calendar table, or something that can help you group the ChildTable StartDate values into weeks? If so you could just group by the ChildTable RecordID and WeekNumber in your subquery, and return the WeekNumber as well. That would probably be easier to write and run than writing 52 different window function expressions.
You could put the values of your variables @WeekXX into a 3 column table:
WeekNumber tinyint,
WeekStartDate Date,
WeekEndDate Date
then joining ChildTable to that should be fairly easy.