To start, here's SQL to create this data:

CREATE TABLE file_list

(

file_ID int identity,

file_status varchar(10),

customer_name varchar(50),

Direction varchar(3),

First_Directory varchar(100),

Original_filename varchar(150)

)

INSERT INTO File_List

values('Active','P&G','I', '\\XYZ', 'MonthlyReport'),

('Passive','Dabur','I','\\XYZ','WeeklyReport'),

('Active','Nirma','O','\\abc','DailyReport')

CREATE TABLE file_dest

(

file_dest_id int identity,

file_id int,

final_directory varchar(100),

output_filename varchar(100),

delivery_type varchar(10)

)

insert into File_Dest

VALUES

(1,'\\GHI', 'MonthlyReport', 'FTP'),

(3,'\\KLM','WeeklyReport', 'FTP'),

(1,'\\GHI','DailyReport', 'SFTP')

create table File_Schedule

(

file_she_id int identity,

file_id int,

start_date datetime,

First_Notify char(1),

Second_Notify char(1),

Due_Day varchar(200),

Schedule_Type varchar(100)

)

INSERT INTO file_schedule VALUES

(1,'12/30/13 0:00','Y','N','Last Monday','Every 28 Days'),

(1,'12/30/13 0:00','Y','Y','Last Day-1','Day(s) EachMonth'),

(3,'12/30/13 0:00','N','N','Thursday','Weekly')

Here are some things wrong with your database

1 - don't store the same string over and over, store it in a table and store an id. Examples 'MonthlyReport' and 'FTP'.

2 - use bit for boolean values, not Y and N

3 - Your values seem to assume a human will read this data, wouldn't it be better to try to automate your process ? How does code make sense of 'every 28 days' and 'Last Day-1' ?

4 - as far as I can tell, you want SQL to parse strings like 'weekly' and '3 per week' and , and on that basis, to work out what files fall under that schedule, for today. That is insane, it's a hideous design and will always be slow and messy. You need to rethink all of this, this is not procedural code, it's set based operations. What you're asking could be done, but a more sane approach would be to store two things:

the date of the next operation and

the number of days between operations

Then every time the operation is due ( which would then be simple to check ), you just store the next date, using the number of days, added to today.

How do you even know what days to run on, if the value is 'three per week' or '8 per month' ?

This is completely unworkable. You should have learned a bit about SQL before starting to try to use it, because right now, you need to throw this all away and start again.