We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
building a query from one table with multiple betweens and or ors
Posted on 2012-12-26
Hello-
I have a very complex (well I think it is complex anyway) query I need written. I am running MSSQL 2005.
I am wishing to have a query tell me the tasks that need to be completed at a specific time. These tasks are all unique and there are about 75 different tasks that should show up at various times of the week and or year and or time of day.
For example: I have "clean baseboards" in the "tasksubject" field and "a more descriptive explanation" in the "taskbody" field. This task should only be done between the hours of 16:00 and 23:59 hours in the month of December only and only on a Friday.
I plan to run this query 3 times a day to produce a result. I just need the query in a view format and I can do the rest. The query will be ran @ 00:05 @ 8:05 and @ 16:05 every day.
The other data for the time field is 17:00
The data in the month fields s
Here is the table with the following columns (I have all data types set to nvarchar(MAX) I can easily change that if needed): I also put the common data that would be in each of the fields)
tasksubject (text and always different)
taskbody (text and always different)
dayofweek (Only a MON, TUE, WED, THU, FRI, SAT, SUN - but it could have more than one not just one)
month (Only a 1,2,3,4,5,6,7,8,9,10,11,12
time (always in military time and just XX:XX)
So with the example I have above it has the following data in each column:
tasksubject: clean baseboards
taskbody: a more descirptive explanation
dayofweek: FRI
month: 12
time: 17:00
Another example:
tasksubject: clean toaster
taskbody: clean out the toaster in the breakroom
dayofweek: MON, THU, SAT
month: 1,2,3,4,5,6,7,8,9,10,11,12
time: 11:00
This above example would create a result on Monday, Tuesday, and Saturday every month at 11 AM.
I will be running this query like I mentioned above 3 times a day so I think a between statement would be used for the date time.
If you have questions please let me know. I am sure this is a lot of work and I really appreciate it.
Ben
I have a very complex (well I think it is complex anyway) query I need written. I am running MSSQL 2005.
I am wishing to have a query tell me the tasks that need to be completed at a specific time. These tasks are all unique and there are about 75 different tasks that should show up at various times of the week and or year and or time of day.
For example: I have "clean baseboards" in the "tasksubject" field and "a more descriptive explanation" in the "taskbody" field. This task should only be done between the hours of 16:00 and 23:59 hours in the month of December only and only on a Friday.
I plan to run this query 3 times a day to produce a result. I just need the query in a view format and I can do the rest. The query will be ran @ 00:05 @ 8:05 and @ 16:05 every day.
The other data for the time field is 17:00
The data in the month fields s
Here is the table with the following columns (I have all data types set to nvarchar(MAX) I can easily change that if needed): I also put the common data that would be in each of the fields)
tasksubject (text and always different)
taskbody (text and always different)
dayofweek (Only a MON, TUE, WED, THU, FRI, SAT, SUN - but it could have more than one not just one)
month (Only a 1,2,3,4,5,6,7,8,9,10,11,12
time (always in military time and just XX:XX)
So with the example I have above it has the following data in each column:
tasksubject: clean baseboards
taskbody: a more descirptive explanation
dayofweek: FRI
month: 12
time: 17:00
Another example:
tasksubject: clean toaster
taskbody: clean out the toaster in the breakroom
dayofweek: MON, THU, SAT
month: 1,2,3,4,5,6,7,8,9,10,11,12
time: 11:00
This above example would create a result on Monday, Tuesday, and Saturday every month at 11 AM.
I will be running this query like I mentioned above 3 times a day so I think a between statement would be used for the date time.
If you have questions please let me know. I am sure this is a lot of work and I really appreciate it.
Ben
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
3
3
16 Comments
I think you're going to have to use dynamic sql if you want to use your existing data structure - and since that's the case, I would build this as a stored procedure rather than a view.
This is a stored procedure that will find tasks that match the month, day of the week, and have a time that is less than 8 hours away from the run time of the procedure.
CREATE PROCEDURE sp_myProcedure AS
EXEC ('select * from mytable
where [month] = datepart(month,getdate())
and convert(datetime,[time]) between
convert(datetime,left(conv
convert(datetime,left(conv
and dayofweek like ' +'''%'+ substring(datename(weekday
You'll want to replace the bold sections with whatever is appropriate.
This is a stored procedure that will find tasks that match the month, day of the week, and have a time that is less than 8 hours away from the run time of the procedure.
CREATE PROCEDURE sp_myProcedure AS
EXEC ('select * from mytable
where [month] = datepart(month,getdate())
and convert(datetime,[time]) between
convert(datetime,left(conv
convert(datetime,left(conv
and dayofweek like ' +'''%'+ substring(datename(weekday
You'll want to replace the bold sections with whatever is appropriate.
Give me a couple of hours to review this. I believe I understand what you are asking. I have only done a stored procedure a couple of times. Ill let you know how it works. Thank you so much!
I get the following error:
Msg 102, Level 15, State 1, Procedure frontdeskProcedure, Line 8
Incorrect syntax near 'substring'.
Msg 102, Level 15, State 1, Procedure frontdeskProcedure, Line 8
Incorrect syntax near 'substring'.
Sorry... use this for the procedure code instead (execute it first to make sure I'm not missing anything else).
--CREATE PROCEDURE sp_myProcedure AS
declare @day as varchar(5)
set @day = (select substring(datename(weekday
declare @sql as varchar(500)
set @sql =
'select * from mytable
where [month] = datepart(month,getdate())
and convert(datetime,[time]) between
convert(datetime,left(conv
convert(datetime,left(conv
and dayofweek like ' +'''%'+ @day +'%'''
exec (@sql)
--CREATE PROCEDURE sp_myProcedure AS
declare @day as varchar(5)
set @day = (select substring(datename(weekday
declare @sql as varchar(500)
set @sql =
'select * from mytable
where [month] = datepart(month,getdate())
and convert(datetime,[time]) between
convert(datetime,left(conv
convert(datetime,left(conv
and dayofweek like ' +'''%'+ @day +'%'''
exec (@sql)
No error this time.
However, no results were produced. Here is what is what I have in my table right now:
Clean baseboards This is instructions on how to clean baseboards WED 12 16:00
Clean carpet this is a test instruction WED 12 17:00
Wash Windows Task of cleaning a window THR 12 17:00
It should have two that produced and nothing did?
However, no results were produced. Here is what is what I have in my table right now:
Clean baseboards This is instructions on how to clean baseboards WED 12 16:00
Clean carpet this is a test instruction WED 12 17:00
Wash Windows Task of cleaning a window THR 12 17:00
It should have two that produced and nothing did?
If you changed your dayofweek column to a number like you have with your months then this could easily be achieved using a view, I think.
I changed it to this :
Clean baseboards This is instructions on how to clean baseboards 5 12 16:00
Clean carpet this is a test instruction 4 12 17:00
Wash Windows Task of cleaning a window 6 12 17:00
Still zero results.
Clean baseboards This is instructions on how to clean baseboards 5 12 16:00
Clean carpet this is a test instruction 4 12 17:00
Wash Windows Task of cleaning a window 6 12 17:00
Still zero results.
First let me know if you can change the structure of your table?
Is TaskSubject simply a shorted heading for TaskBody?
eg..
"tasksubject: clean toaster" will always be "taskbody: clean out the toaster in the breakroom"?
If this is the case then you may want to consider splitting your table into "Task" and "TaskFrequency"
This way you do not have to store multiple days and months in the same column and so can simply hit your data with a query. You can also amend the times and days that each task needs to be carried out without having to resort to string manipulation on those dodgy multivalued columns.
Is TaskSubject simply a shorted heading for TaskBody?
eg..
"tasksubject: clean toaster" will always be "taskbody: clean out the toaster in the breakroom"?
If this is the case then you may want to consider splitting your table into "Task" and "TaskFrequency"
This way you do not have to store multiple days and months in the same column and so can simply hit your data with a query. You can also amend the times and days that each task needs to be carried out without having to resort to string manipulation on those dodgy multivalued columns.
The table can be changed, however....
tasksubject is actually data that will be put into a subject line email.
taskbody is actually the body of the email.
Once I get the query producing the values I want then I will create a cursor to generate emails for me to a ticket system we have.
I don't need help making the cursor as I do those all the time I just can't figure out how to make this query work.
We are taking multiple checklists staff has to follow and putting this all into a ticket system that will assign tasks to them on down days rather than them having to go work off a checklist.
Thanks
Ben
tasksubject is actually data that will be put into a subject line email.
taskbody is actually the body of the email.
Once I get the query producing the values I want then I will create a cursor to generate emails for me to a ticket system we have.
I don't need help making the cursor as I do those all the time I just can't figure out how to make this query work.
We are taking multiple checklists staff has to follow and putting this all into a ticket system that will assign tasks to them on down days rather than them having to go work off a checklist.
Thanks
Ben
If it helps I can store items in the time field instead of 11:00 it can be 11. Looking for suggestions.
For the suggestion I posted:
You should be getting some results. There are probably fields that matching in the WHERE clause because of how they are (or are not) being converted. You can try taking out all of the WHERE criteria, and then adding them back in one by one to see which field conversion is the problem.
A view can't be used because of the variable declaration.
A variable MUST be declared because of the method of storing the days of the week .
Because the day of the week column is a string that contains more than one day of the week value ('Wed, Thu, Fri' or '3, 4, 5')
the WHERE statement needs to state that
dayofweek like '%Thu%' -- or with numbers
dayofweek like '%4%'
the only way to build the string like that is with dynamic sql.
If you change the way you store that field's data it will let you get rid of the dynamic sql and write the query as a view.
You should be getting some results. There are probably fields that matching in the WHERE clause because of how they are (or are not) being converted. You can try taking out all of the WHERE criteria, and then adding them back in one by one to see which field conversion is the problem.
A view can't be used because of the variable declaration.
A variable MUST be declared because of the method of storing the days of the week .
Because the day of the week column is a string that contains more than one day of the week value ('Wed, Thu, Fri' or '3, 4, 5')
the WHERE statement needs to state that
dayofweek like '%Thu%' -- or with numbers
dayofweek like '%4%'
the only way to build the string like that is with dynamic sql.
If you change the way you store that field's data it will let you get rid of the dynamic sql and write the query as a view.
Featured Post
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
INTRODUCTION:
While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Suggested Courses
Course of the Month7 days, 1 hour left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.