In a perfect world, reports would be pulled on the same day and time every week to achieve maximum consistency. Unfortunately, mere mortals cannot maintain this regularity without fail. It only takes one unforeseen circumstance to throw the reports off.
Fortunately, the GETDATE() function replaces human fallibility and pulls a dynamic weekly performance data report starting on any specified day. This provides flexibility to the program and maintains consistency. A real win-win!
To specify the report start date use: WHERE CAST(table.Date as DATE) < CAST(DATEADD(DAYS, -DATEPART(dw, getdate())-1), getdate()) as DATE). If this code is run on a Tuesday, the results will begin on the most recent Monday and so on until Sunday.