SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query Help?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattj
Starting Member

3 Posts

Posted - 02/08/2013 :  11:40:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 02/08/2013 :  12:13:18  Show Profile  Reply with Quote
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 - 02/08/2013 :  12:25:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 02/08/2013 :  12:39:29  Show Profile  Reply with Quote
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 - 02/08/2013 :  12:53:07  Show Profile  Reply with Quote
.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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 02/08/2013 :  13:03:24  Show Profile  Reply with Quote
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

Edited by - James K on 02/08/2013 13:03:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000