I want to find all work orders that's in 'K030' wo_status and in '4' doc_status that has not had a labor scan in the past 24 hours. The wh.due_date also needs to be within the next two days.
I believe the script below should work but I am getting incorrect data. Each work order has multiple scans in the wo_labor table yet I want only the most recent scan.
By just looking at the query, can you see why it is not working correctly or how I can build a more efficient query?
select wh.doc_no, MAX(wl.ADDED_DTE ) from WO_HDR wh outer apply WO_LABOR wl where wh.wo_status = 'K030' and wh.doc_status = '4' and wh.DUE_DATE < GETDATE()+2 and wl.wo_doc_no = wh.doc_no and wl.added_dte < getdate()-1 group by wh.doc_no order by wh.doc_no