How to change the week Macro to start on Monday for Reporting

From time to time certain sites will require the changing of the week macro to start on a Monday instead of a Sunday.

To do this please follow the below guide, It is relatively simple but care must be taken as there are scripts in MS SQL that need to be modified.

START 

Step one is to open MS SQL and navigate to the PITRAMReporting Database

Expand the database, then expand Programmability and again the Function.

Change the following - 

Start Date:

           WHEN 'LAST WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) + 6 ), @today )

           WHEN 'THIS WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) - 1 ), @today )

change to

           WHEN 'LAST WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) + 5 ), @today )

           WHEN 'THIS WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) - 2 ), @today )

You will need to then change the end date - 

End Date:

           WHEN 'LAST WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) ), @today )

change to:

           WHEN 'LAST WEEK' then DATEADD( day, -1 * ( DATEPART( dw, @today ) -1), @today )

no need to change ‘THIS WEEK’ for the end date.

Save the script and then re run the reports to verify that the start date is correct.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access