Posted by Erland Sommarskog on 10/03/07 22:08

Mike (darthvadertojabuahahahaha@gmail.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_Nazwa !
>=''). 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_Miejscowosc,adr_NIP, dok_DataWyst
> FROM vwDok4FSFZGrid
> INNER JOIN adr__Ewid
> ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
> WHERE
> adr__Ewid.adr_Nazwa !=''
> AND
> (dok_DataWyst >= convert(datetime,'10/03/2007'))
> GROUP BY dok_PlatnikId, adr_Nazwa,
> adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
>
> Is there any way to negate this set?

A complete guess:

SELECT adr_Nazwa, adr_NazwaPelna,adr_Adres,adr_Miejscowosc,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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]