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
below86
below86
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 2711
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
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 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.
below86
below86
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 2711
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
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 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.
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19651 Visits: 7660
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
below86
below86
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 2711
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.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Christian Graus
Christian Graus
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 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.
below86
below86
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 2711
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...

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
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