SELECT T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART MAIN_COMPONENT, X.ORDER
FROM TRANS_DETAIL T LEFT OUTER JOIN WORKORDER W ON T.TASK = W.TASK
LEFT OUTER JOIN
(
SELECT T.TRANS_DATE TRX_DATE, T.ORDER, T.LINE_NO, T.PART MAIN_COMPONENT
FROM TRANS_DETAIL T
) X ON X.MAIN_COMPONENT = W.PART AND X.TRX_DATE > T.TRANS_DATE
WHERE T.PART = 'VX112045'
TRANS_DETAIL
TRANS_ID TRANS_DATE PART ORDER LINE
1686844 2011-01-02 VX112045 NULL NULL
1723978 2011-07-29 7788-0111 9468 18
1893537 2011-10-29 998832-110 10967 1
Could you please provide DATA (not images of data) something like my example aboveTRANS_ID TRANS_DATE PART TASK ORDER LINE PO TYPE
1661395 10/18/2010 VX112045 612200 Receipt
1661463 10/18/2010 VX112045 Adj/Out
1661464 10/18/2010 VX112045 Adj/In
1686844 2/1/2011 VX112045 12544 Issue
1858703 6/12/2013 VX112045 623122 Receipt
1865792 7/15/2013 VX112045 13766 Issue
1021731 8/18/2006 7788-0111 5739 7 Issue
1081460 12/20/2006 7788-0111 6302 4 Issue
1083059 12/22/2006 7788-0111 6651 11 Issue
1083755 12/27/2006 7788-0111 6302 4 Issue
1723978 7/29/2011 7788-0111 9468 18 Issue
1893537 10/29/2013 998832-110 10967 1 Issue
1893538 10/29/2013 998832-110 10967 1 Issue
1928274 3/24/2014 998832-110 10977 1 Issue
1948024 6/25/2014 998832-110 11492 1 Issue
1996076 3/11/2015 998832-110 12095 1 Issue
2039768 9/3/2015 998832-110 R74599 1 Issue
SELECT T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART MAIN_COMPONENT, MIN(X.ORDER) ORDER
FROM TRANS_DETAIL T LEFT OUTER JOIN WORKORDER W ON T.TASK = W.TASK
LEFT OUTER JOIN
(
SELECT T.TRANS_DATE TRX_DATE, T.ORDER, T.LINE_NO, T.PART MAIN_COMPONENT
FROM TRANS_DETAIL T
) X ON X.MAIN_COMPONENT = W.PART AND X.TRX_DATE >= T.TRANS_DATE
WHERE T.PART = 'VX112045'
GROUP BY T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART
TASK PART
12544 7788-0111
13766 998832-110
WITH TRANS_DETAIL AS
(
SELECT '1661395' TRANS_ID, TO_DATE('10/18/2010', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '' TASK, '' CUST_ORDER, '' LINE, '612200' PO, 'Receipt' TRX_TYPE FROM DUAL UNION ALL
SELECT '1661463' TRANS_ID, TO_DATE('10/18/2010', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '' TASK, '' CUST_ORDER, '' LINE, '' PO, 'Adj/Out' TRX_TYPE FROM DUAL UNION ALL
SELECT '1661464' TRANS_ID, TO_DATE('10/18/2010', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '' TASK, '' CUST_ORDER, '' LINE, '' PO, 'Adj/In' TRX_TYPE FROM DUAL UNION ALL
SELECT '1686844' TRANS_ID, TO_DATE('02/01/2011', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '12544' TASK, '' CUST_ORDER, '' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1858703' TRANS_ID, TO_DATE('06/12/2013', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '' TASK, '' CUST_ORDER, '' LINE, '623122' PO, 'Receipt' TRX_TYPE FROM DUAL UNION ALL
SELECT '1865792' TRANS_ID, TO_DATE('07/15/2013', 'MM/DD/YYYY') TRANS_DATE, 'VX112045' PART, '13766' TASK, '' CUST_ORDER, '' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1021731' TRANS_ID, TO_DATE('08/18/2006', 'MM/DD/YYYY') TRANS_DATE, '7788-0111' PART, '' TASK, '5739' CUST_ORDER, '7' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1081460' TRANS_ID, TO_DATE('12/20/2006', 'MM/DD/YYYY') TRANS_DATE, '7788-0111' PART, '' TASK, '6302' CUST_ORDER, '4' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1083059' TRANS_ID, TO_DATE('12/22/2006', 'MM/DD/YYYY') TRANS_DATE, '7788-0111' PART, '' TASK, '6651' CUST_ORDER, '11' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1083755' TRANS_ID, TO_DATE('12/27/2006', 'MM/DD/YYYY') TRANS_DATE, '7788-0111' PART, '' TASK, '6302' CUST_ORDER, '4' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1723978' TRANS_ID, TO_DATE('07/29/2011', 'MM/DD/YYYY') TRANS_DATE, '7788-0111' PART, '' TASK, '9468' CUST_ORDER, '18' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1893537' TRANS_ID, TO_DATE('10/29/2013', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, '10967' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1893538' TRANS_ID, TO_DATE('10/29/2013', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, '10967' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1928274' TRANS_ID, TO_DATE('03/24/2014', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, '10977' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1948024' TRANS_ID, TO_DATE('06/25/2014', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, '11492' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '1996076' TRANS_ID, TO_DATE('03/11/2015', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, '12095' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL UNION ALL
SELECT '2039768' TRANS_ID, TO_DATE('09/03/2015', 'MM/DD/YYYY') TRANS_DATE, '998832-110' PART, '' TASK, 'R74599' CUST_ORDER, '1' LINE, '' PO, 'Issue' TRX_TYPE FROM DUAL
),
WORKORDER AS
(
SELECT '12544' TASK, '7788-0111' PART FROM DUAL UNION ALL
SELECT '13766' TASK, '998832-110' PART FROM DUAL
)
SELECT T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART MAIN_COMPONENT, MIN(X.CUST_ORDER) CUST_ORDER
FROM TRANS_DETAIL T LEFT OUTER JOIN WORKORDER W ON T.TASK = W.TASK
LEFT OUTER JOIN
(
SELECT T.TRANS_DATE TRX_DATE, T.CUST_ORDER, T.LINE, T.PART MAIN_COMPONENT
FROM TRANS_DETAIL T
ORDER BY T.TRANS_DATE
) X ON X.MAIN_COMPONENT = W.PART AND X.TRX_DATE >= T.TRANS_DATE
WHERE T.PART = 'VX112045'
GROUP BY T.TRANS_ID, T.TRANS_DATE, T.PART, T.TASK, W.PART
ORDER BY T.TRANS_ID;
Not really. I'm still not 100% sure about your requirement. You say you can use PART to join them but the PART column in the two sample datasets don't match. It appears the main component from one matches the part from the other.
I'm also not sure if what you posted is sample data from the two tables. If so, what are your expected results?
You also don't define which one of those data sets is which table. I can guess but I really hate to.
Define "closest date". What if you have a date that is a single day behind and a row two days ahead. Which one do you want?
If you define it by the "next" date that is equal to or greater than, you might get by with a MIN in the subquery.