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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query Help?

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.
AND
2. 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 day

This query assumes the answer is no, yes to these 2 questions, respectively.
SELECT
rr.candidate_id
FROM
Rollup_Records rr
INNER JOIN candidate_activity ca ON ca.candidate_id = rr.candidate_id
WHERE
ca.[user_name] = 'MATT J'
AND rr.Rollup_Name='MATT.0024'
GROUP BY
rr.candidate_id
HAVING
MAX(ca.last_activity) < DATEADD(dd ,-90 ,GETDATE())
Go to Top of Page

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.

Go to Top of Page

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.
Go to Top of Page

mattj
Starting Member

3 Posts

Posted - 2013-02-08 : 12:53:07
.net
A 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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -