>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
No, you don't need the EXCEPT part at all. The first query with HAVING() handles your case. Also, GETDATE() vs. CURRENT_TIMESTAMP should not give you any difference in performance. The only difference is ANSI standards that Celko is a big advocate of. The CURRENT_TIMESTAMP is ANSI, that's why I switched in my code to using it. It should be absolutely no difference which one to use.
If it's not broken, fix it until it is.
My Blog