Currently writing a query which will be used to see where abouts on our production line a certain item is by showing what the last completed 'Resource' was. (resources on our system are just different areas of the assembly line eg, metal fabrication, spray shop, electrical wiring, testing etc.)
I've written a query that gives me ALL completed resources on unfinished jobs, but as each job has several completed resources on it the table that gets output still needs refining.
SELECT T0."DocNum", T2."U_CustomerRef", T1."DocNum", T6."ItemCode", CONCAT(T5."firstName", T5."lastName"), T4."U_Time", T4."U_Date", T4."U_StopTime", T4."U_StopDate" FROM ORDR T0 INNER JOIN RDR1 T2 ON T0."DocEntry" = T2."DocEntry" LEFT OUTER JOIN "NL_LIVE"."OHEM" "OHEM" ON T0."OwnerCode"="OHEM"."empID" LEFT OUTER JOIN OWOR T1 ON T1."OriginAbs" = T0."DocEntry" AND T2."ItemCode" =T1."ItemCode" INNER JOIN WOR1 T6 ON T1."DocEntry" = T6."DocEntry" INNER JOIN OITM T3 ON T2."ItemCode" = T3."ItemCode" LEFT OUTER JOIN "NL_LIVE"."@OCHLABBOOK" T4 ON T4."U_ProdOrderNo" = T1."DocNum" AND T4."U_ProdOrderLine"=T6."LineNum" LEFT OUTER JOIN "NL_LIVE"."OHEM" T5 ON T4."U_StartEmp"=T5."U_UniqueID" WHERE T0."DocStatus"='O' AND T6."ItemType"=290 AND T4."U_Action"<>'Started' AND T4."U_Action"<>'Stopped' AND T4."U_Action"<>'Paused'
I've tried doing SELECT MAX on the results using the date and time, but that leaves me with only one result in the table as there aren't any groupings for each area it just gives me the latest results for the whole table not each individual job/Document. In this image you can see I've drawn red borders showing that certain parts should be grouped, essentially what i need is a way of selecting the most recent date from each smaller group of data within the Query. Each subset needs to be grouped by Document number (column 3 document number not column 1)
Large shaded red area is just to cover employee names and isn't relevant to the question
Thanks in advance for any help given. If more information is required I can go into more detail of table structures etc.