Find values NOT in a table - 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 2000 Forums

Transact-SQL (2000)

Find values NOT in a table

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2008-01-31 : 03:54:05

Hi Everyone

I'm having a problem and I can't get it to work for the life of me.

Two tables:
[UNITS]
[BILLINFO]

Units stores a list of unit numbers (1, 2, 3, 4 etc...)
Units also stores a list of telephone numbers. Each [UNIT] has:
UNITNO
TELEPHONENO
OLDTELNO

BillInfo stores the telephone bill for the company.
[BILLINFO] has lots of billing info in it, as well as a matching unit number and finalnumber (which is the telephone no).

I'm able to match units to the BILLINFO and charge them for their calls.

Now we want a report of units that's not matching BILLINFO. Eg, I'm looking for a list that will say:

So basically, the query should say:
Show me all records in UNITS that where UNITS.TELEPHONENO OR UNITS.OLDTELNO didn't match the BILLINFO.FINALNUMBER field. Why is it so difficult to get this to work??? Hehe.

Here's what I've done so far but - this is a list of units that did actually make calls for the specified period. The units without calls are left out - but I want the opposite result.

SELECT units.unitno, SUM(CASE WHEN billinfo.finalnumber = units.telephoneno OR
billinfo.finalnumber = units.oldtelno THEN 1 END) AS TotalCalls
FROM units LEFT OUTER JOIN
billinfo ON units.unitno = billinfo.unitno
WHERE (billinfo.complexid = 1) AND billinfo.monthno = 11 AND billinfo.yearno = 2007
GROUP BY units.unitno

I wanted to change this query to say where TotalCalls = 0.

Any help will be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 04:03:26

[code]SELECT * FROM
(
SELECT units.unitno, SUM(CASE WHEN billinfo.finalnumber = units.telephoneno OR
billinfo.finalnumber = units.oldtelno THEN 1 END) AS TotalCalls
FROM units LEFT OUTER JOIN
billinfo ON units.unitno = billinfo.unitno
WHERE (billinfo.complexid = 1) AND billinfo.monthno = 11 AND billinfo.yearno = 2007
GROUP BY units.unitno
)tmp
WHERE tmp.TotalCalls=0[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources