Oracle SQL - Join by nearest date

>>Make sense?

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.

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

Select allOpen in new window

Could you please provide DATA (not images of data) something like my example above
Provide the sample data as text or inserts, provide the column names and table names.

We need data from all of the tables independently, not combined together as query results.
In particular I think you need to show us how the WORKORDER table provides a translation of every part (sub-component) to some main-component

Sorry here's the result from the table.

TRANS_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 allOpen in new window

With the above, once I get the "Task" ID, I query the Workorder table to get the Main Component.  That Main component is use to search for sales order that has been issued/shipped out.  Since there's no direct link, I am using the closest date.

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

Select allOpen in new window

With the above query, I manage to get it to work somewhat.  For some reason, if I have date that's the same but different order, it pulls both or even if it's just a day one after another.  I just want it to pull the first record and that's it.

Thanks.
Does that set of rows comes direct from the TRANS_DETAIL table?

We also need to understand the WORKORDER table, I mentioned this last time:

In particular I think you need to show us how the WORKORDER table provides a translation of every part (sub-component) to some main-component

This remains true.
I can't see how you build a bill-of-materials (main part & sub parts)

Yes, that entire set of rows comes from TRANS_DETAIL table and no where else.

The "WORKORDER" table consists of a ton of fields, but the only 2 fields that are of importance is the TASK and PART field.  If you look at my query, I did a join to the WORKORDER table from TRANS_DETAIL table using the "TASK" field in the TRANS_DETAIL table and pull the PART value.  Basically, the WORKORDER table has the main component.  The TRANS_DETAIL has the sub components that was issued to the Job.

Once I extract the Main Component from the WORKORDER, I can then do a nested query to pull from TRANS_DETAIL table to find the record that the Main Component was issued to, which has the sales order number.

As I said, my 2nd query with the grouping and min was working but I am running into issues when I have more than one sales order within same date or day or two afterward.  It is pulling twice instead of just the one.

Here's what it would look like in the WORKORDER table with just the 2 fields I need and value.

TASK	        PART
12544	7788-0111
13766	998832-110

Select allOpen in new window

If I can get just the first value it pulled, I am golden.  However that is a bit of a challenge.

Is this for an Oracle database?

ORDER is a reserved word so you can only have a column named that if you used double quotes to create it.

The latest SQL you posted has aliases in the GROUP BY so it will not run as posted.

>>the main query if the date is 2/1/2011

Is this a parameter you will be passing in?

You still never defined "closest date".  Does that meant the first date AFTER the provided date?  What if one doesn't exist in the table, do you want the closest less than the provided date?

I have to concur, In Oracle I cannot get any query to work if using ORDER as a column name; unless specifically quoted as "ORDER" and then it becomes case sensitive as well. Not a great idea.

Sorry that was my bad.  Trying to copy snippets of my code and deleted the actual field name by accident.  The "ORDER" is "CUST_ORDER".   Here's what my code and it is working somewhat.  Having a few records not giving me just the minimum like this example, so will keep playing.  Thanks guys.

Here's a snippet of my code as reference.

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;

Select allOpen in new window