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 Tuesday, January 7, 2014 8:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
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.

Post #1528501
Posted Tuesday, January 7, 2014 2:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
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.
Post #1528683
Posted Tuesday, January 7, 2014 3:29 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
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

Post #1528700
Posted Tuesday, January 7, 2014 3:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
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.
Post #1528702
Posted Tuesday, January 7, 2014 3:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1528703
Posted Tuesday, January 7, 2014 3:44 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
debajit (1/5/2014)
Hi Christian,

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


Based on this statement is why I looked at the Due_Day field and ignored the Schedule_Type.
I agree the Schedule_Type is useless for what is shown so far.
Post #1528709
Posted Tuesday, January 7, 2014 3:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
As far as I can see, the due day is either the starting day, or it's the next day, which is meant to be calculated, using that string value. Working out the next day based on that string is entirely the requested task.

I think everyone who is getting pulled in to this question is realising the same thing - this system is unworkable the way it's been designed, the broad variety of potential inputs needs to be standardised, put in it's own table or group of tables, and turned in to something it's possible to write reasonable SQL against.
Post #1528710
Posted Tuesday, January 7, 2014 3:55 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Christian Graus (1/7/2014)
As far as I can see, the due day is either the starting day, or it's the next day, which is meant to be calculated, using that string value. Working out the next day based on that string is entirely the requested task.



Could be, I read it as it was the day it was due.

I'm just going to wait until the requestor provides more info/feedback before I waist any more time on it.

Have a good one...
Post #1528712
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse