Struggling with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Struggling with a query

Author

Topic

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 05:23:13

Hi i'm new to sql quries and i'm strugggling with a query and can't
seem to find any help, hopefully someone here can help me ;)

ok let me explain my application:
the admin can create forms/tasks, now the field names and field types are saved to tblTaskForm. Once the captured forms/tasks are saved to tblTask. (in a nutshell)

now the admin can assign these forms/tasks by users, by users position and assign it as a weekly,monthly,daily,yearly task.

i'm struggling with the assigning of weekly etc. part

my query so far:
sqlWeekly = "select tblTaskForm.* "
sqlWeekly = sqlWeekly & "from tblTaskForm "
sqlWeekly = sqlWeekly & "where "
sqlWeekly = sqlWeekly & "("
'this part calls tasks assigned to weekly
sqlWeekly = sqlWeekly & "recurringType = 'weekly' "
'this part is to see what day of the week to show
sqlWeekly = sqlWeekly & "AND datepart(w,recurring) = 3) "
'this part checks if the task is assigned to the user
sqlWeekly = sqlWeekly & "OR (assignValue = '" & Session("user_id") & "' "
sqlWeekly = sqlWeekly & "OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '" & Session("user_id") & "') "
sqlWeekly = sqlWeekly & "OR assignValue = 'all'"
sqlWeekly = sqlWeekly & ")"
'this part checks whether the task has been captured, if it has then don't display
sqlWeekly = sqlWeekly & "AND taskFormID NOT IN"
sqlWeekly = sqlWeekly & "("
sqlWeekly = sqlWeekly & "SELECT DISTINCT taskFormID FROM tblTask WHERE taskAgent = '" & Session("user_id") & "' "
sqlWeekly = sqlWeekly & ")"

1.now i need this code to show on the day of the week, every week as selected by the field recurring.
2.if the task has been captured then don't display until nxt week again.

Thanks for any help
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 05:33:23

It would help if you could provide some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 05:46:48

ok for now i'll just post the important data from both tables.

tblTaskForm (table that holds the tasks/forms)
taskFormID - 1
recurringType - weekly
recurring - 2006/12/18 (date for beginning of task, and for which day of week to display)
assignValue - all

tblTask (table holding captured task/form)
taskID - 14
taskFormID - 1
taskAgent - 1 (users ID)
taskDate - 2006/12/20 (date task was captured)

The 18th was a tuesday, so every tuesday the task must show.
if the task is captured, do not show again until nxt week teusday.

those are the two problems i can't solve
Shem

p.s Peter Larsson, name sounds very familiar

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 08:08:09

any body of a solution to my problem, i've been stuck on this for ages

please please

Shem

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 08:51:36

Never concatenate together a sql string like that. Always write it out with parameters and assign the parameters values. please see: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx for more information. What you are doing makes your sql less readable, harder to debug and edit, and more error prone.

The best thing to do is to fire up query analyzer, put in your sql statement directly, and then test it, substituting constants while you are debugging and testing for the parameter values. Doesn't that make things much easier to look at end to tweak, rather than playign with your web application and writing strong concatenation code ? Only when you have a code, solid working SQL statement that works should you then incorporate it into your application. And, when you do, you should either a) put that sql statement into a stored procedure or b) use parameters, as I demonstrate in the link provided.

- Jeff

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 09:21:35

just read your article..
i'm going to have to learn to start writing my sql queries in that manner
thanks for the article

if i re-wrote my query, would you be able to help me with my problems

Thanks
Shem

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 09:53:29

sure. I suspect that if you try to develop and test it in query analyzer directly, you will also find that it will be so much easier to test and debug that you may even come up with the answer on your own. But either way, this forum is the place to be if you need help with SQL.

- Jeff

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 09:59:28

my problem is that i'm new to this, and self taught so there's many commands and ways of doing things right
that I don't know of that i find it hard to find a way to write the sql to do what i want it to.

the sql i have so far works, but i want to add those to things to filter it further, but i'm clueless or
rather, not skilled enough to know how to go about it, i've been writing simple queries to pull records
out of databases, this is the first complicated query i've had to write.

i'm going to go and try and re-write it the way you have suggested.

basically i don't know how to write that, if its been a week since
this was captured, then only show it....

ok i think i just realised how to solve it, going to try some thing quick, will let you know if it works

Shem

Shem
Yak Posting Veteran

62 Posts

Posted - 2006-12-20 : 10:23:54

well i just came up with one solution

after the task has been captured, it won't display until seven days has passed..

thanks for the advice Jeff
though i'm sure the next bug will rear its ugly head soon.

Cheers
Shem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources