Outer apply issue

Transact-SQL
1

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
2

Can you post some sample data, what you are getting with your query using the sample data and what you want to get?

Please don't forget CREATE TABLE and INSERT INTO statements

Note that APPLY is meant to be used with a correlated sub query, so

SELECT a, b, c FROM A
OUTER APPLY B
WHERE A.a = B.a

is equivalent to

SELECT a, b, c FROM A
CROSS JOIN B
WHERE A.a = B.a

which is probably not what you want.

However:

SELECT a, b, c FROM A
OUTER APPLY ( 
     SELECT b FROM B
     WHERE A.a = B.a
) B(b)

is a correlated sub query. That may be what you are looking for (and should process far fewer rows if the columns in the WHERE clause are indexed)

3

Hi I went to the trouble of creating data .. Looking at the result set it is giving what he is looking for. What am I missing ? Thanks

SET statistics time, io ON; 

IF Object_id('tempdb.dbo.#wo_hdr', 'U') IS NOT NULL 
  DROP TABLE #wo_hdr; 

IF Object_id('tempdb.dbo.#wo_labor', 'U') IS NOT NULL 
  DROP TABLE #wo_labor; 

CREATE TABLE #wo_hdr 
  ( 
     docno      INT NULL, 
     wo_status  VARCHAR(100) NULL, 
     doc_status VARCHAR(100) NULL, 
     due_date   DATETIME NULL 
  ) 

CREATE TABLE #wo_labor 
  ( 
     docno      INT NULL, 
     added_date DATETIME NULL 
  ) 

INSERT INTO #wo_hdr 
SELECT 1, 'K030', '4', '2016-12-27'

INSERT INTO #wo_labor 
SELECT 1, '2016-12-24'  UNION ALL 
SELECT 1, '2016-10-23'  UNION ALL  
SELECT 1, '2016-06-21'  UNION ALL  
SELECT 1, '2016-09-21' 


SELECT wh.docno, 
       Max(wl.added_date) 
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.docno = wh.docno 
       AND wl.added_date < Getdate() - 1 
GROUP  BY wh.docno 
ORDER  BY wh.docno 

SET statistics time, io OFF;

1 Like
4

Great! Now, reread my previous post on OUTER APPLY and correlated sub queries, then rewrite your query to do it that way.