negate RESULT SET, all inactive client

[Mike]

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.

I use INNER JOIN to join invoice view (vwDok4FSFZGrid ) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_N azwa !
=''). I select only invoices with date after the interesting date.

So now I can list all active clients but i can't negate this result
set to get all inactive clients:

SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna, adr_Adres,adr_M iejscowosc,adr_ NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid. dok_PlatnikId=a dr__Ewid.adr_Id Obiektu
WHERE
adr__Ewid.adr_N azwa !=''
AND
(dok_DataWyst >= convert(datetim e,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna, adr_Adres,adr_M iejscowosc,adr_ NIP, dok_DataWyst

Is there any way to negate this set?

1. Sorry for my English
2. I would appreciate any help
:)

[Erland Sommarskog]

Mike (da************ ***********@gma il.com) writes:

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.

I use INNER JOIN to join invoice view (vwDok4FSFZGrid ) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_N azwa !
=''). I select only invoices with date after the interesting date.

So now I can list all active clients but i can't negate this result
set to get all inactive clients:

SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna, adr_Adres,adr_M iejscowosc,adr_ NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid. dok_PlatnikId=a dr__Ewid.adr_Id Obiektu
WHERE
adr__Ewid.adr_N azwa !=''
AND
(dok_DataWyst >= convert(datetim e,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna, adr_Adres,adr_M iejscowosc,adr_ NIP, dok_DataWyst

Is there any way to negate this set?

A complete guess:

SELECT adr_Nazwa, adr_NazwaPelna, adr_Adres,adr_M iejscowosc,adr_ NIP
FROM adr__Ewid a
WHERE a.adr_Nazwa <''
AND NOT EXISTS (SELECT *
FROM vwDok4FSFZGrid v
WHERE v.dok_PlatnikId = a.adr_IdObiektu
AND v.dok_DataWyst >= '20070310')

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx