I have a Proc that runs reports(not SSRS), Weekly reports runs every Monday and Monthly on the first of the month. I have a @reportDate variable, which suppose to calculate the 7 previous days on Monday to run Weekly reports and previous month days on the 1 of every month to run Monthly reports.
I have this problem, For Weekly reports, should the report not run on Monday for whatever reasons, maybe the server was off, and it the process kicks off on a Tuesday or Thursday for that matter, the code still have to use the Monday date and run the weekly report, same goes for Monthly.
How do I solve this, thanks.
How are you calculating the dates for the 7 days now?
The following will give you the date for the Monday immediately preceding today (unless today is Monday, in which case it will return today's date)
DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())/7*7,0)
Use that in place of whatever you were doing to pick up today's date and calculate the previous 7 days. For example, this will subtract 7 days from the Monday (thus giving you the Monday prior to that)