TechSapphire Thursday, 2024-03-28, 6:32 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Main » 2014 » June » 2 » Get scheduled records from database - make report scheduler
    0:12 AM
    Get scheduled records from database - make report scheduler

    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

    Category: Database | Views: 23903 | Added by: Admin | Tags: Make, report scheduler, Build, schedule records | Rating: 0.0/0
    Total comments: 1
    1 conpinteyrozxy  
    0
    builtinnikq02

    Only registered users can add comments.
    [ Registration | Login ]
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2024