I need to display the current status of a part (‘approved’ or ‘not approved’) I am setting up the status table as MaterialNo, ToolID, Status, Asofdate
the idea is I may have several tools approved and several not approved. I want to be able to see the most recent status for each tool but I don’t want to see the historical status.
have tried to use MAX(Asofdate) and group by MaterialNo, ToolID, and Status but it returns all of the status history for each.
SELECT MaterialNo, ToolID, PPAPStatus, MAX(Asof) as "AsOf" FROM [MfgDataCollector].[dbo].[PPAPStatus] Group By MaterialNo, ToolID, PPAPStatus
The Status Table has this data:
MaterialNo ToolID Status Asofdate 52748677 1 Not approved 2019-10-10 52748677 1 approved 2019-10-13 52748677 2 approved 2019-10-14
I Want to see:
MaterialNo ToolID Status Asofdate 52748677 1 approved 2019-10-13 52748677 2 approved 2019-10-14
One solution that usually has very good performance is a correlated subquery:
select p.* from [MfgDataCollector].[dbo].[PPAPStatus] p where p.asof = (select max(p2.asof) from [MfgDataCollector].[dbo].[PPAPStatus] p2 where p2.MaterialNo = p.MaterialNo and p2.ToolID = p.ToolID );
For optimal performance, you want an index on
(MaterialNo, ToolID, asof).