Below is script you can use to get scheduled records:
-- Table you requre to hold schedule time and other information
-- you can add other columns too to suite your requirements.
-- Main columns are scheduletype : it hold information if schedule is daily, weekly or monthly
-- Scheduletime : Time when you need to send report
-- Scheduleday : Day of week or month you want to fire report
-- for week Sunday holds value 1, monday 2, ... saturday equal 7 and repeat for next week from 1 to 7
-- for month value for scheduleday is for day of month means 1 is 1st day of month
-- for daily its value doesn't matter
-- other columns are just dependent on requirement
create table #scheduletable
--Note: I am using temporary table. Use table according to your needs.
-- Now insert some values using query below into table
insert into #scheduletable values('report1','2:32:00','monthly',getdate(),2,2),
-- Now for getting records form database you need three things in 3 parts query
-- schedule time as you will run scheduler after fixed interval suppose 30 minutes
-- so you need query to pull reports details which are scheduled in time interval
-- current time to current time + 30 minutes
-- also records for scheduled day for current week and month
-- example if today is sunday then records with scheduleday 1 for scheduletype monthly and daily
-- will come if their schedule time come between current time range we are using
-- Note: I am using static time so that query could pull records according to entries in table
-- Start time and end time declaraction and initiallization
-- you have to use query above each time to initialize dynamic time to get records in real time
--Start batch to execute queries
--getdate() method get you current date and time
--select from next line to comment end line. All line are part of one query.
declare @starttime time(0)='2:14:00'
-- you have use
-- declare @starttime time(0)=convert(time(0),getdate())
-- end time we will calculate with adding 30 minutes to current time
declare @endtime time(0)=dateadd(minute,30,@starttime)
--below is query to get day of week
--below is query to get day of month
--query to get records from table. Query is using UNION ALL as query is combination of 3 queries.
--one query gets you daily schedule records
--one gets you monthly
--one gets you weekly
select * from #scheduletable where scheduletype='Daily' and scheduletime between @starttime and @endtime
select * from #scheduletable where scheduletype='Weekly' and scheduletime between @starttime and @endtime
select * from #scheduletable where scheduletype='monthly' and scheduletime between @starttime and @endtime