April 1, 2016 at 12:45 am
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 currently is set to GETDATE(), 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.
April 1, 2016 at 4:25 am
maybe a few ideas to get you started
SELECT thedate,
DATENAME(dw, thedate),
DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, thedate)), 0) startthisweek,
DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, thedate)), -7) startpreviousweek,
DATEADD(mm, DATEDIFF(mm, 0, thedate) - 1, 0) startpreviousmonth
FROM
( -- Test Data
SELECT thedate = GETDATE() - 7 UNION ALL
SELECT thedate = GETDATE() - 6 UNION ALL
SELECT thedate = GETDATE() - 5 UNION ALL
SELECT thedate = GETDATE() - 4 UNION ALL
SELECT thedate = GETDATE() - 3 UNION ALL
SELECT thedate = GETDATE() - 2 UNION ALL
SELECT thedate = GETDATE() - 1 UNION ALL
SELECT thedate = GETDATE() UNION ALL
SELECT thedate = GETDATE() + 1 UNION ALL
SELECT thedate = GETDATE() + 2 UNION ALL
SELECT thedate = GETDATE() + 3
) x;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 1, 2016 at 6:57 am
Hi
You can also create a table called Report_Schedule for example. Load your report names in and dates for the monthly and or weekly reports to get started with.
Then in your procedure you set your variable date to the value specified in your table for whichever report and on the weekly or monthly date.
Then after report procedure has finished update your Report_Schedule table with the new date for the next week. Same will apply for monthly.
For example:
SET @reportDate = (SELECT Weekly_Run_Date FROM dbo.Report_Schedule WHERE Report = 'Test_Report') -- weekly date to use to get 7 previous days
/*
Report Code here
*/
UPDATE Report
SET Weekly_Run_Date = DATEADD(week,1,Weekly_Run_Date)
FROM dbo.Report_Schedule Report
WHERE Report.Report = 'Test_Report'
So from above your date will be correct for the next time your procedures gets executed regardless on what day of the week that may be.
Hope this helps.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply