Facebook

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
(
reportname varchar(100),
scheduletime time,
scheduletype varchar(10),
scheduledate date,
scheduleday int,
userid int
)
–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), 
(‘report1′,’2:15:00′,’Daily’,getdate(),0,1),
(‘report2′,’2:20:00′,’weekly’,getdate(),2,2),
(‘report3′,’3:30:00′,’weekly’,getdate(),1,3),
(‘report1′,’2:32:00′,’monthly’,getdate(),1,2),
(‘report1′,’2:40:00′,’daily’,getdate(),0,3)


— 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 
–select datepart(“dw”,getdate())
–below is query to get day of month
–select datepart(“dd”,getdate())


–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
union all
select * from #scheduletable where scheduletype=’Weekly’ and scheduletime between @starttime and @endtime
and scheduleday=datepart(“dw”,getdate())
union all
select * from #scheduletable where scheduletype=’monthly’ and scheduletime between @starttime and @endtime
and scheduleday=datepart(“dd”,getdate())-1

–end line

Leave a Reply

Your email address will not be published. Required fields are marked *