>>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?
>
>What indexes do you have on the salesinvoice and SalesInvoiceItem table?
>
>Also...can you post a screen shot of the entire execution plan?

Hi,
Execution Plan for my query is attached - but Naomi's suggestion runs in 5 secs whereas mine was taking 25 so that's good enough.

Indexes on
SalesInvoice.Account (FK)
SalesInvoiceItem.ProductCode and SalesInvoice.

No indexes on Sales.Invoice date but we aren't allowed to modify the DB :-{

Thx,
Viv