Select Statement Last Receipt Date

Transact-SQL
1

I have a table iminvtrx which holds records of inventory receipts

Trying to do a select statement that will grab the last receipt date and the qty received.

I've tried:
select t.item_no, MAX(t.doc_dt) as LastReceiptDate, t.quantity
from iminvtrx_sql t join imitmidx_sql i on t.item_no = i.item_no
where t.source = 'R' and t.doc_type = 'R' and i.pur_or_mfg = 'P'
group by t.item_no, t.doc_dt, t.quantity

But its returning multiple records for the same item_no.
I would only expect one record for each item_no

2

You might try something like:

WITH MyMax AS ( select t.item_no, MAX(t.doc_dt) as LastReceiptDate from iminvtrx_sql t where t.source = 'R' and t.doc_type = 'R' group by t.item_no ) SELECT * FROM MyMax M INNER JOIN iminvtrx_sql t ON M.item_no = t.item_no AND M.LastReceiptDate = t.doc_dt join imitmidx_sql i on t.item_no = i.item_no where i.pur_or_mfg = 'P';

3

It works but runs very slow. Any way to speed it up?

4

What indexes do you have?

5 
select t.item_no, t.doc_dt as LastReceiptDate, t.quantity
from (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY item_no ORDER BY doc_dt DESC) AS row_num
    FROM iminvtrx_sql
    WHERE source = 'R' and doc_type = 'R'
) as t
inner join imitmidx_sql i on t.item_no = i.item_no
where t.row_num = 1 and i.pur_or_mfg = 'P'