January 4, 2014 at 12:43 am
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
January 4, 2014 at 3:45 am
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/
January 4, 2014 at 4:46 am
Hi Kapil,
Please find sample data.
File_List
File_ID File_Status Customer_NameDirectionFirst_DirectoryOriginal_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_DirectoryOutput_FileNameDelivery_Type
1 1\\GHI MonthlyReport FTP
2 3\\KLM WeeklyReport FTP
3 1\\GHI DailyReportSFTP
File_Schedule
File_Sche_ID File_ID Start_Date First_Notify Second_Notify Due_Day Schedule_Type
1 112/30/13 0:00 Y N Last MondayEvery 28 Days
2 112/30/13 0:00 Y Y Last Day-1Day(s) EachMonth
3 312/30/13 0:00 N N Thursday
Weekly
Thanks,
Debajit
January 5, 2014 at 6:57 pm
debajit (1/4/2014)
Hi Kapil,Please find sample data.
File_List
File_ID File_Status Customer_NameDirectionFirst_DirectoryOriginal_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_DirectoryOutput_FileNameDelivery_Type
1 1\\GHI MonthlyReport FTP
2 3\\KLM WeeklyReport FTP
3 1\\GHI DailyReportSFTP
File_Schedule
File_Sche_ID File_ID Start_Date First_Notify Second_Notify Due_Day Schedule_Type
1 112/30/13 0:00 Y N Last MondayEvery 28 Days
2 112/30/13 0:00 Y Y Last Day-1Day(s) EachMonth
3 312/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 [/url]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 5, 2014 at 9:05 pm
That last response is *insane*. The OP said they have no idea how to write SQL. The size of their database, is irrelevant.
January 5, 2014 at 9:22 pm
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.
January 5, 2014 at 9:51 pm
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
January 5, 2014 at 9:57 pm
"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.
January 5, 2014 at 10:06 pm
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
January 5, 2014 at 10:11 pm
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.
January 7, 2014 at 8:02 am
Since 'File ID' is on all three tables you should be able to join using that, then just pull in the values from each table.
Select a.*, b.*, c.*
from table_1 a
inner join table_2 b on
a.file_id = b.file_id
inner join table_3 c on -- changed to table_3
a.file_id = c.file_id
Just add a where clause to pull just the dates you want, or something like this if I'm understanding this.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 7, 2014 at 2:41 pm
Yes, you have missed the point. The join is easy. Parsing strings like '3 times a week' and 'daily' and 'every second Monday' and then deciding if that rule fits today, is the complex part.
January 7, 2014 at 3:29 pm
If the values you are trying to match are set values like the example data you provided, then you should be able to come up with coding to match each criteria.
Here is my attempt at the values shown in the example data:
select a.*, b.*, c.*
from #File_List a
inner join #File_Dest b on
a.file_id = b.file_id
inner join #File_Schedule c on
a.file_id = c.file_id
where Due_Day = datename(dw, getdate()) -- if value in Due_day like 'Thursday'
or (getdate() = (dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, -day(getdate()), getdate())))) -- 'Last Day - 1'
or (datename(dw, getdate()) = 'Monday'
and day(getdate()) > 23
and month(getdate()) not in (1, 3, 5, 7, 8, 10, 12)) -- last Monday
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 7, 2014 at 3:32 pm
Yes, but the starting point is, which days fall under '3 days a week' or '8 times a month'. That's why I asked the OP to define business rules, as well as noting that the range of different strings he wants parsed, will result in a very hard to read query, similar to what you posted, but a LOT bigger.
January 7, 2014 at 3:34 pm
I realize you don't want to redesign your system, but this is code-parsing at best, painful at worst.
There are a few components you need here, particularly if you want to keep user-friendly terms for your users instead of having them setup things with significant complexity.
First, you need to get these standardized. You want to make sure all options are in a table of their own that you can add attributes to for future calculation inclusions.
Next, you will want a calendar table that has attributes associated with the different days. These are flags that will correspond to your standardized choices table.
For example, in your calendar table, you'd have a date, then some extra flags, such as "First Monday" 0/1, "Second to last Monday" 0/1... etc.
Then, when you're deciding which reports will run on what day, you'd go to the calendar table, find the active flags, go to the standardized list table, find out who's active for those flags, then finally take those values backwards into your report list to determine what to run.
This isn't a quick query to help you with, particularly not urgently, it's a design overhaul.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply