>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
You can also try this variation:
select SI.AccountCode,MAX(SI.InvoiceDate) AS Latest FROM SalesInvoice SI
where exists (select 1 from SalesInvoiceItem IV where IV.InvoiceNumber = SI.InvoiceNumber
and ProductCode = '56.GTR003.001.01')
GROUP BY SI.AccountCode
HAVING MAX(SI.InvoiceDate) < dateadd(month, - 1, CAST(CURRENT_TIMESTAMP as DATE))
May be slightly faster.
If it's not broken, fix it until it is.
My Blog