SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Urgent help need to create query


Urgent help need to create query

Author
Message
debajit
debajit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Hi All,

I am new to SQL server. Please help me to create a query for following:

I have 3 tables File_List, File_Dest and File_Schedule in SQL server.

File_List Contains following Columns
File_ID (Unique_ID), File_Status (Active/Passive), Customer_Name, Direction (I/O), First_Directory, Original_FileName.

File_Dest contains following columns:
File_ID, File_Dest_ID (Unique_ID), Final_Directory, Output_FileName, Delivery_Type,

File_Schedule contains
File_Schedule_ID (Unique_ID), File_ID, Start_Date, First_Notify(Y/N), Second_Notify(Y/N), Due_Day and Schedule_Type

Due_Day can be Wednesday, Tuesday, Thursday, Sunday, Second Wednesday, Second Monday, Saturday, Monday, Last Monday, Last Day-1, Last Day, Friday, First Wednesday, First Sunday, First Monday, First Day, etc.

Schedule_Type can be Weekly, Weekdays, Variable, Qtr Third Month, Qtr First Month, Number Per Week, Number Per Month, Number Per Day, Every 28 Days, Day(s) In Month(s), Day(s) EachMonth, Day(s) Each Week, Day(s) Each Month, Daily/Hourly, Daily, Bi-Weekly, Bi-Monthly, etc.

Now I need to find today’s scheduled files with all the columns from all three tables. Please help me to create a query which can perform above task.

Thanks,
Khanin
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2767
It will be easy for us to help you if you provide some sample data........

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
debajit
debajit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Hi Kapil,

Please find sample data.

File_List
File_ID File_Status Customer_Name Direction First_Directory Original_FileName
1 Active P&G I \\XYZ MonthlyReport
2 Passive Dabur I \\XYZ WeeklyReport
3 Active Nirma O \\abc DailyReport



File_Dest
File_Dest_ID File_ID Final_Directory Output_FileName Delivery_Type
1 1 \\GHI MonthlyReport FTP
2 3 \\KLM WeeklyReport FTP
3 1 \\GHI DailyReport SFTP



File_Schedule
File_Sche_ID File_ID Start_Date First_Notify Second_Notify Due_Day Schedule_Type
1 1 12/30/13 0:00 Y N Last Monday Every 28 Days
2 1 12/30/13 0:00 Y Y Last Day-1 Day(s) EachMonth
3 3 12/30/13 0:00 N N Thursday
Weekly

Thanks,
Debajit
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17665 Visits: 6431
debajit (1/4/2014)
Hi Kapil,

Please find sample data.

File_List
File_ID File_Status Customer_Name Direction First_Directory Original_FileName
1 Active P&G I \\XYZ MonthlyReport
2 Passive Dabur I \\XYZ WeeklyReport
3 Active Nirma O \\abc DailyReport



File_Dest
File_Dest_ID File_ID Final_Directory Output_FileName Delivery_Type
1 1 \\GHI MonthlyReport FTP
2 3 \\KLM WeeklyReport FTP
3 1 \\GHI DailyReport SFTP



File_Schedule
File_Sche_ID File_ID Start_Date First_Notify Second_Notify Due_Day Schedule_Type
1 1 12/30/13 0:00 Y N Last Monday Every 28 Days
2 1 12/30/13 0:00 Y Y Last Day-1 Day(s) EachMonth
3 3 12/30/13 0:00 N N Thursday
Weekly

Thanks,
Debajit




I believe he was suggesting that you post your DDL and sample data in consumable form:
Forum Etiquette: How to post data/code on a forum to get the best help


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 97
That last response is *insane*. The OP said they have no idea how to write SQL. The size of their database, is irrelevant.
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 97
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.
debajit
debajit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Hi Christian,

These tables are created long back and there is flow where Files needs to be sent to client based on the Schedule in File_Schedule table. Currently they want to incorporate a missed notification of file which are not delivered as per schedule.

For that process, we thought of creating a table which will store all the daily schedule details along with Due time. For that purpose, we thought of Selecting all data from these three tables based on criteria for that day.

If there would have been only schedule type daily then we can use Join and select those data and insert them into Daily Schedule table. But as there are different schedule type and different Due_Day, so we are not able determine a select query which can select all the Daily Schedule for that day.

Please help me to generate a Select query which can determine all Daily Schedules for current Day which might fall in any of Schedule Type and Due_Day.




Thanks,
Khanin
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 97
"Please help me to generate a Select query which can determine all Daily Schedules for current Day which might fall in any of Schedule Type and Due_Day."

"Schedule_Type can be Weekly, Weekdays, Variable, Qtr Third Month, Qtr First Month, Number Per Week, Number Per Month, Number Per Day, Every 28 Days, Day(s) In Month(s), Day(s) EachMonth, Day(s) Each Week, Day(s) Each Month, Daily/Hourly, Daily, Bi-Weekly, Bi-Monthly, etc."

I'm sorry. I am happy to spend a lot of time digging in to questions for people. This one is just impossible. As I said, if there's a 'number per month', given that the number of days in the month varies, it's NEVER going to divide evenly in to all months. So, how do I know which days it should run on ? What does ' Daily/Hourly' mean ? Every hour ? But, you're talking about something that is looking at days as a level of granularity, right ? What does 'Variable' mean in terms of working out what days ?

The SQL is going to be ugly as sin, based on this awful table structure. But, for it to be even possible for someone to answer this, you need to define business rules because they are not self evident. I don't know what the correct result looks like in a lot of these cases, so I can't possibly write SQL to generate it.
debajit
debajit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Hi Christian,

I agree with you, it is very difficult to figure out from such a design but my problem is changing design of these table will impact my application. Is it possible to this by Procedural approach. If we write a Stored Procedure and Schedule that Stored Procedure daily at a certain time.


Thanks,
Debajit
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 97
I repeat. The first issue is not how complex this query will be to deal with how broken your table design is. Your first issue is, defining business rules because what you're asking for is ambiguous to say the least.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search