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>>
>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.AccountCodeOr
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.