If one value doesn't exist, no values should

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)

If one value doesn't exist, no values should

Author

Topic

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-01-05 : 16:49:46

I have a table that records values from a call tracking software. There can also be multiple records for each call id. What I would like to be able to do is that if there are 3 assignments for one call id, and one of those records has a resolution date missing, to exclude that record from a query.

callid recvddate assignee dateacknow dateresolv
112546 2007-01-15 Bob 2007-01-16 2007-01-16
112546 2007-01-15 Tim 2007-01-17 2007-01-17
112546 2007-01-15 Jerry 2007-01-17
13265 2007-06-15 Bob 2007-06-16 2007-06-18
13265 2007-06-15 Janet 2007-06-16 2007-06-19
13265 2007-06-15 Jerry 2007-06-16 2007-06-18
13265 2007-06-15 Jim 2007-06-16 2007-06-18

So if I were to query this, I only want to return 13265.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-05 : 16:59:27

Would this work?

SELECT callid, recvddate, assignee, dateacknow, dateresolv
FROM YourTableName
WHERE dateresolv is not null

that would give you all the records that had a dateresolv date.

To get all the records without a resolve date simply change the where
clause to dateresolv is null

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 17:01:48

quote:
Originally posted by revdnrdy

Would this work?

SELECT callid, recvddate, assignee, dateacknow, dateresolv
FROM YourTableName
WHERE dateresolv is not null

that would give you all the records that had a dateresolv date.

To get all the records without a resolve date simply change the where
clause to dateresolv is null

I think he wants to exclude all CallID records if even one of those CallIDs is null, not just the one row where it is null.

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-01-05 : 17:03:14

Unfortunately that wouldn't work. If I use DATERESOLV IS NOT NULL then it still returns that one callid. I don't want to return a callid if there is still a dateresolv'd that needs to be set. (If that makes sense)

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-05 : 17:30:57

Hmm ok well maybe use two tables..

1. Create a table to hold all ID's of all records with NULL in dateresoled.

Select callid into TableA from TableB
where dateresolv is null

2. Perform a left join to get records from TableB that do not have a callid in TableA. This will exclude everything that has at least 1 null value for callid.

SELECT * FROM TableB
LEFT OUTER JOIN TableA
ON TableB.callid = TableA.callid
WHERE TableA.callid IS null

something like that, note I didnt have time to test this..

r&r

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 17:41:50

I think I may have what you're looking for:

SELECT CallID, RecvdDate, Asignee, DateAcknow, DateResolv
FROM YourTable
WHERE CallID NOT IN (SELECT CallID FROM YourTable WHERE DateResolv IS NULL)

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 17:48:32

Also, if you want just the CallIDs that have no unresolved dates you can do this:

SELECT DISTINCT CallID
FROM YourTable
WHERE CallID NOT IN (SELECT CallID FROM YourTable WHERE DateResolv IS NULL)

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-01-05 : 18:09:54

quote:
Originally posted by Skorch

I think I may have what you're looking for:

SELECT CallID, RecvdDate, Asignee, DateAcknow, DateResolv
FROM YourTable
WHERE CallID NOT IN (SELECT CallID FROM YourTable WHERE DateResolv IS NULL)

That is is. Monday after the holidays. Brain fart.

Thanks.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 18:21:36

quote:
Originally posted by RyanAustin

quote:
Originally posted by Skorch

I think I may have what you're looking for:

SELECT CallID, RecvdDate, Asignee, DateAcknow, DateResolv
FROM YourTable
WHERE CallID NOT IN (SELECT CallID FROM YourTable WHERE DateResolv IS NULL)

That is is. Monday after the holidays. Brain fart.

Thanks.

No problem :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-05 : 18:33:03

You should use NOT EXISTS instead of NOT IN. Compare the execution plans to see which is more efficient.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources