Having issues with this SQL query

Question

SELECT jobnumber, ProductName, ItemNumber  from Product
inner join heading on Product.jobkeyid = heading.jobkeyid
WHERE DateDelivery = CAST( GETDATE() as date) AND heading.JobKeyID IN 
(
SELECT tracking.ItemKeyID, MAX(StageID) stageid from Tracking
INNER join Production on Tracking.JobKeyID = Production.JobKeyID And production.ItemKeyID = Tracking.ItemKeyID
group by  tracking.ItemKeyID
)

Main goal is to get the last scanned ID of each item using todays date

i have the date bit down but the last scanned is the issue and it needs to be in a sub query as it has multiple records

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Oct 29 '21 at 13:57

score 0 · Accepted Answer · answered Oct 29 '21 at 14:00

you need to provide sample data and desired output but I think this is what you need to do: :

SELECT
    jobnumber,
    ProductName,
    ItemNumber,
    stageid
from Product
inner join heading 
    on Product.jobkeyid = heading.jobkeyid
    and DateDelivery = CAST(GETDATE() as date)
inner join (
     select
        tracking.ItemKeyID,
        MAX(StageID) stageid
     from Tracking
     join Production 
        on Tracking.JobKeyID = Production.JobKeyID
        And production.ItemKeyID = Tracking.ItemKeyID
     group by tracking.ItemKeyID
) t on heading.JobKeyID  = t.ItemKeyID

Having issues with this SQL query

1 Answers1