>Goal: To get all accounts that have purchased a specific product more then one month ago but have *not* purchased within the last month.
>
>I've tried the following but it's very slow where a lot of accounts have purchased the product in the last month:

WITH Temp_CTE (AccountCode,InvoiceDate)
>AS
>(
>SELECT SI.AccountCode,MAX(SI.InvoiceDate) FROM SalesInvoiceItem IV 
>INNER JOIN SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
>WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate >= DATEADD(MONTH, - 1,GETDATE())
>GROUP BY SI.AccountCode
>)
>SELECT SI.AccountCode,MAX(SI.InvoiceDate) AS Latest  FROM SalesInvoiceItem IV 
>INNER JOINSalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
>LEFT JOIN Temp_CTE ON SI.AccountCode = Temp_CTE.AccountCode
>WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate < DATEADD(MONTH, - 1,GETDATE()) AND Temp_CTE.AccountCode IS NULL
>GROUP BY SI.AccountCode

>
>I'm useless at SQL - can anyone suggest a way of speeding this up?

Where are couple of ways:

SELECT SI.AccountCode,MAX(SI.InvoiceDate) as LastInvoiceDate FROM SalesInvoiceItem IV 
INNER JOIN SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate < DATEADD(MONTH, - 1,GETDATE())
GROUP BY SI.AccountCode
EXCEPT
SELECT SI.AccountCode,MAX(SI.InvoiceDate) as LastInvoiceDate FROM SalesInvoiceItem IV 
INNER JOIN SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate  BETWEEN DATEADD(MONTH, - 1,CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY SI.AccountCode