negate RESULT SET, all inactive client

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?
1. Sorry for my English
2. I would appreciate any help
:)I would write it like this ...
SELECT *
FROM CustomerAddressTable AS InactiveCustomers
WHERE NOT EXIST ( SELECT * FROM InvoiceView AS I
INNER JOIN CustomerAddressTable AS C
ON I.ID = C.ID
WHERE InactiveCustomers.ID = C.ID
AND I.Date >= Convert(datetime,'08/03/2007')
AND I.Date <= Convert(datetime,'10/03/2007')
)
Sorry I didn't use your fields, I didn't understand all of them; I hope this
gives you some ideas.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mike" wrote:
> 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?
> 1. Sorry for my English
> 2. I would appreciate any help
> :)
>|||This might help you:
SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid dok1
INNER JOIN adr__Ewid adr
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
WHERE adr__Ewid.adr_Nazwa !=''
AND NOT EXISTS(
SELECT * FROM vwDok4FSFZGrid dok2
WHERE dok2.dok_PlatnikId = dok1.dok_PlatnikId
AND dok2.dok_DataWyst >= convert(datetime,'10/03/2007')
)
GROUP BY dok_PlatnikId,
adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP,
dok_DataWyst
=====================Regards,
Steve
www.foxville.ch
On Oct 4, 12:00 am, Mike <darthvadertojabuahahah...@.gmail.com> wrote:
> 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?
> 1. Sorry for my English
> 2. I would appreciate any help
> :)