Exclude values - 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)

Exclude values

Author

Topic

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2009-06-22 : 13:48:23

How would I go about excluding values from a query when I cannot seem to use distinct or group by properly?

I have the following:

SELECT callid, recvd, emailid, custid, firstname, lastname, loginid, callsource
--INTO #CI_Survey
FROM (
SELECT top 50 percent c.callid,
getdate() as recvd,
p.emailid,
p.custid,
p.firstname,
p.lastname,
p.loginid,
c.callsource
FROM Prod.dbo.calllog c tablesample(1000000 rows)
LEFT OUTER JOIN Prod.dbo.[profile] p ON c.custid = p.custid
WHERE c.closeddate = CONVERT(VARCHAR(10), GETDATE() -3, 120)
AND c.custtype = 'Employee'
AND p.loginid <> 'noaddress'
AND p.emailid IS NOT NULL
AND p.SurveyOptOut is not null
ORDER BY checksum(newid()), c.callid) XXX
GROUP BY callid, recvd, emailid, custid, firstname, lastname, loginid, callsource
HAVING COUNT(custid) = 1
ORDER BY custid

The query pulls a random sample of all trouble tickets closed on a given day (I'm using the -3 because we don't close any on the weekends). I only want distinct custid to show up, and when I run this I get multiple custid. I really don't care about the callid that gets returned, I just don't want to see more then 1 custid per day that I run this.

Thanks,

Ryan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-22 : 14:31:05

If there are different callids within a custid, then you'll get a record for each callid. If it isn't necessary for your results, just drop it out of your Select statement and Group By statement and you should be okay.

Jim

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-22 : 15:12:20

I can believe that each retrieved record for one custid hase the same
emailid, custid, firstname, lastname
but what is about
recvd, callsource, loginid?
If you want only one record per custid this would go like this:

SELECT max(callid) as callid, max(recvd) as recvd, emailid, custid, firstname, lastname, max(loginid) as loginid, max(callsource) as callsource
--INTO #CI_Survey
FROM (
SELECT top 50 percent c.callid,
getdate() as recvd,
p.emailid,
p.custid,
p.firstname,
p.lastname,
p.loginid,
c.callsource
FROM Prod.dbo.calllog c tablesample(1000000 rows)
LEFT OUTER JOIN Prod.dbo.[profile] p ON c.custid = p.custid
WHERE c.closeddate = CONVERT(VARCHAR(10), GETDATE() -3, 120)
AND c.custtype = 'Employee'
AND p.loginid <> 'noaddress'
AND p.emailid IS NOT NULL
AND p.SurveyOptOut is not null
ORDER BY checksum(newid()), c.callid) XXX
GROUP BY custid, emailid, firstname, lastname
ORDER BY custid