I'm trying to create a view for a report so that I can easily keep track of all of the backups that I have scheduled on a server. All of these backups are scheduled in subplans of a maintenance plan, so the job names all start with "Backup.".
The problem that I've run into is that I want to easily be able to see when the job is scheduled to run again and how often it's scheduled. The sysschedules table has the fields freq_type and freq_interval. The definition of their values can be found here. As you can see these fields are, for lack of a better way to describe, accumulative. I have no idea on how to, in a set-based solution, set it up so that I can see the names of the day.
What'd I'd like to be able to see is something like "Executes every 3 days" or "Executes every monday, tuesday, friday".
How can I write this so that I'm not using layers of case statements to get what I want?
Here's my code so far:
select j.job_id, j.name, s.next_run_date, s.next_run_time, ss.* from msdb.dbo.sysjobs j left outer join msdb.dbo.sysjobschedules s on j.job_id=s.job_id left outer join msdb.dbo.sysschedules ss on s.schedule_id=ss.schedule_id where j.name like 'backup.%'