>Oops, replied to myself by mistake.
>
>>>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

>
>Or
>
>

>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' 
>GROUP BY SI.AccountCode
>HAVING MAX(SI.InvoiceDate) < DATEADD(MONTH, - 1,GETDATE())

>
>BTW, ProductCode needs an alias.

Hi,
Your first suggestion didn't remove the accounts for those who ordered in the last month. I assume that for the second you meant:

SELECT SI.AccountCode,MAX(SI.InvoiceDate) as LastInvoiceDate FROM LV1_Planglow.LV1_DB.SalesInvoiceItem IV 
INNER JOIN LV1_Planglow.LV1_DB.SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
WHERE ProductCode='56.GTR003.001.01' 
GROUP BY SI.AccountCode
HAVING MAX(SI.InvoiceDate) < DATEADD(MONTH, - 1,GETDATE())
EXCEPT
SELECT SI.AccountCode,MAX(SI.InvoiceDate) as LastInvoiceDate FROM LV1_Planglow.LV1_DB.SalesInvoiceItem IV 
INNER JOIN LV1_Planglow.LV1_DB.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

This works great - 5secs compared to my original 25secs and gives the same results as my original query (554 rows). But there seems to be a slight anomaly in both versions:

If I run the two selects individually then the first ( previous orders) returns 655 rows and the second (ordered in last month) returns 107 rows.
So I would expect 548 rows in the final result rather than the 544 which we are getting.....

UPDATE: Forgot to mention that using GETDATE() rather than using CURRENT_TIMESTAMP in the above knocks ~1 sec off the execution time