Join? - 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 2005 Forums

Transact-SQL (2005)

Join?

Author

Topic

Tyork
Starting Member

6 Posts

Posted - 2011-01-17 : 13:30:22

So I have a table:

Call_History

That displays information about all calls made. I'm interested in the following fields:

phone
status

My goal is to aggregate a list of 'phone' that has NEVER received a status of:

('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')

Therefore the query:

SELECT Phone, COUNT(*) as PhoneAttempt
FROM Call_History
WHERE Status NOT IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
GROUP BY Phone

would not work.

I'm thinking the easiest way to approach this is to perform some type of join where I take all of 'phone' from the call_history table where phone is not in the following sub-query

SELECT Phone
FROM Call_History
WHERE Status IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
GROUP BY Phone

Any help with this would be greatly appreciated. It's my first time posting to this forum so if this is in the wrong spot let me know.

Tyork

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 13:49:37

You got the right idea!

SELECT p.phone
FROM Call_History P
LEFT JOIN
(
SELECT Phone
FROM Call_History
WHERE Status IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
GROUP BY Phone) t
ON
p.phone = t.phone
WHERE t.phone is null

GROUP BY p.phone

Jim

Everyday I learn something that somebody else already knew

Tyork
Starting Member

6 Posts

Posted - 2011-01-17 : 14:03:18

Thanks for the help. 'WHERE t.phone is null' is the key. I couldn't quite wrap my head around how to make the join work correctly.

Tyork

Tyork
Starting Member

6 Posts

Posted - 2011-01-17 : 14:10:05

One additional question:

I'm accustomed to using access queries but have started to use pass through queries in access to take advantage of the server processing power. I'd like to take this to one step further and return records only when the count of attempts has exceeded a given number. I've changed the query to the following but get an error stating that 'AttemptCount' is an invalid column name. I'm sure it's something native to T-SQL that I'm not used to.

SELECT p.phone, count(*) AS AttemptCount
FROM Call_History P
LEFT JOIN
(
SELECT Phone
FROM Call_History
WHERE Status IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
GROUP BY Phone) t
ON
p.phone = t.phone
WHERE t.phone is null AND AttemptCount > 150
GROUP BY p.phone

Do you know what he correct syntax for that would be?

Tyork

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 15:06:14

You can't reference a column by name in the group by clause that you created in a select query, also I think you want to put the attempts in the subquery?

SELECT p.phone, count(*) AS AttemptCount
FROM Call_History P
LEFT JOIN
(
select Phone
from Call_History,count(*) as AttemptCount
where [Status] IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
group by Phone
having count(*) > 150
) t
ON
p.phone = t.phone

GROUP BY p.phone

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources