Difference between two sets of data in SQL Server 2000

Question

I am trying to work out the difference between customers that have been billed in the last 3 months and the whole customer base. I have been able to calculate the customers that have been billed using the following SQL

DECLARE @DateFrom AS DATETIME
SET @DateFrom = 
   CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + 
   '-' + 
   CAST(MONTH(DATEADD(MONTH, -3, GETDATE())) AS VARCHAR(2)) + '-01', 120)

DECLARE @DateTo AS DATETIME
SET @DateTo = 
   CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + 
   '-' + 
   CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-01', 120)

SELECT DISTINCT
    i.CustomerCode
FROM
    Invoice AS i
WHERE
    (i.InvoiceDate > @DateFrom AND i.InvoiceDate < @DateTo)

The table that I will be comparing against will be the Customer table which also has a CustomerCode field.

Thanks in advanced!

EDIT

After spending ages trying to figure this out and just after a few minutes of posting this message here I found a solution. Using the NOT IN clause!

SELECT
    c.CustomerCode
FROM
    [Customer] AS c
WHERE
    c.CustomerCode NOT IN (
SELECT DISTINCT
    i.CustomerCode
FROM
    [Invoice] AS i
WHERE
    (i.InvoiceDate >= @DateFrom AND i.InvoiceDate < @DateTo)) 

In my senario this appears to perform quicker then the steps mentioned below when I tested each in Management Studio.

Answer 1

you can improve your date handling with:

WHERE i.InvoiceDate >= DATEADD(mm,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
    AND i.InvoiceDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

notice I used >= on the start, you are missing rows on the start date with a time of 00:00:00.

to get all the customers that did not have invoices within the last 3 full months, you could also write it this way:

SELECT
    c.CustomerCode
FROM Customer                c
    LEFT OUTER JOIN Invoice  i ON c.CustomerCode=i.CustomerCode
        AND i.InvoiceDate >= DATEADD(mm,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
        AND i.InvoiceDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
WHERE i.CustomerCode IS NULL

Answer 2

To get the customers that have not being billed during some period of time:

SELECT      c.CustomerCode
FROM        [Customer] AS c
LEFT JOIN   [Invoice] AS i
        ON  c.CustomerCode = i.CustomerCode 
        AND i.InvoiceDate > @DateFrom AND i.InvoiceDate < @DateTo
WHERE       i.CustomerCode IS NULL

Though I would replace the @DateFrom and @DateTo date range checks with suggestion of KM to avoid using variables.