SELECT SI.AccountCode, MAX(SI.InvoiceDate) AS Latest FROM SalesInvoiceItem IV INNER JOIN SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber WHERE ProductCode='56.GTR003.001.01' AND MAX(SI.InvoiceDate) < DATEADD(MONTH, - 1,GETDATE()) GROUP BY SI.AccountCode>Goal: To get all accounts that have purchased a specific product more then one month ago but have *not* purchased within 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>