Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Urgent help need to create query Expand / Collapse
Author
Message
Posted Saturday, January 04, 2014 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:33 PM
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
Post #1527788
Posted Saturday, January 04, 2014 3:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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/
Post #1527800
Posted Saturday, January 04, 2014 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:33 PM
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


Post #1527806
Posted Sunday, January 05, 2014 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:35 PM
Points: 3,591, Visits: 5,102
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!
Post #1527951
Posted Sunday, January 05, 2014 9:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
That last response is *insane*. The OP said they have no idea how to write SQL. The size of their database, is irrelevant.
Post #1527959
Posted Sunday, January 05, 2014 9:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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.
Post #1527960
Posted Sunday, January 05, 2014 9:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:33 PM
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
Post #1527961
Posted Sunday, January 05, 2014 9:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
"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.

Post #1527963
Posted Sunday, January 05, 2014 10:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:33 PM
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
Post #1527964
Posted Sunday, January 05, 2014 10:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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.

Post #1527965
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse