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.
Author |
Topic |
mattj
Starting Member
3 Posts |
Posted - 2013-02-08 : 11:40:49
|
Hi all...I'm trying to formulate a SQL query to search our database for a certain group of people.I have a decent start, but can't quite get it finished.My statement in plain English...1. Find candidates whose last_activity BY "user_name" is greater than 90 days old.AND2. Find candidates in rollup_name- 'MATT.0024'(last_activity can be less than 90 days old, if it's by a different user... I need last_activity by a specific user is older than 90 days)What I have so far, which I can't get to work correctly:Candidate_Id IN ( SELECT candidate_id FROM candidate_activity WHERE last_activity < DATEADD(dd ,-90 ,GETDATE()) and user_name = 'MATT J' ) and candidate_id IN ( SELECT candidate_id FROM Rollup_Records WHERE Rollup_Name='MATT.0024' )I'm getting confused by the less than/ greater than sign, because it seems like it's doing the opposite of what I tell it. I'm not sure what I'm doing wrong, but in any case- the above #1 and #2 statements are what I need the DB to return, as an AND meaning the candidate must meet both criteria to pull through in the list...Thanks for any feedback! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 12:13:18
|
Is there another table you are querying, or are these the only two tables that you need data from?Also, do you need 90 days including the time portion, or just the dayThis query assumes the answer is no, yes to these 2 questions, respectively.SELECT rr.candidate_idFROM Rollup_Records rr INNER JOIN candidate_activity ca ON ca.candidate_id = rr.candidate_idWHERE ca.[user_name] = 'MATT J' AND rr.Rollup_Name='MATT.0024'GROUP BY rr.candidate_idHAVING MAX(ca.last_activity) < DATEADD(dd ,-90 ,GETDATE()) |
|
|
mattj
Starting Member
3 Posts |
Posted - 2013-02-08 : 12:25:05
|
I'm only looking in rollup and activity for the criteria to meet, so those should be the two tables I suppose. However I don't know how the DB is organized exactly. I only have access to the web interface for basic user features. So I'm not sure of how the DB stores and organizes what goes where.As for the 90 days, it can be any number, but I'm trying to get something that says last contact by "matt j" is older than 90 days. (or 45, 30, etc- whatever is typed in)I'm not really sure what to do with what you've given me. If it's not obvious, I'm not experienced at SQL by any means. I just need to get this put together to roll out a method for staying in touch with prospective clients/ candidates. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 12:39:29
|
What is the client you are using to connect to the database? Is it a .Net program, or is it a third party application that lets you send adhoc queries to the server, or something else? If you are using a third party application, they may have their own features/limitations built into their system, so a normal SQL query may not work.If you have access to SQL Server Management Studio, use that to connect to the server and run the query against the database. That will give you a sense of what the query is doing. If it seems like it is giving you what you want, then, this can be made into a stored procedure where you can pass parameters for the number of days, user_name and rollup_name. |
|
|
mattj
Starting Member
3 Posts |
Posted - 2013-02-08 : 12:53:07
|
.netA normal sql query I don't believe will work, it may be adhoc, however I apologize I don't have good answers to these questions. I do know there are limitations to some degree...I don't have access to the SQL Server. :(Every time I feel like I make progress, I test it and for whatever reason I doesn't work. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 13:03:24
|
If you are using .Net and ADO.Net you can send a query to the server and get the return values. This MSDN page has a sample. Use the query that I posted instead of the query against dbo.Products that they are using.http://msdn.microsoft.com/en-us/library/dw70f090.aspx |
|
|
|
|
|
|
|