Oracle SQL - Join by nearest date

I am not sure if this is possible, but I need help with a nested query.  I have a transactional table and all transactions happened within this table.  I want to join the nested table with the main table and display the "Order" field.  I can use the "Part" to link, but it is return more records and not give me the correct value.  So next is compared it by the closest date.

Example, the main query if the date is 2/1/2011 look at the nested result and if anything that is equal or closest date and the "Main Component" match then use that one .  So in the screenshot, the 2nd diagram the next closest date is 7/29/2011 and "Main Component" is 7788-0111.  Then it's order 9468.  Next one is 7/15/2013 from main query.  Look at nested query and next closest date is 10/29/2013 and "Main Component" is 998832-110, then use that one.  Make sense?

Example
So here's my query:

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'

Open in new window


The above query return everything after the first date and that's not what I want.  I only want it to return the closest date and that's it.
holemaniaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>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.
0
PortletPaulEE Topic AdvisorCommented:
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

Open 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
0
holemaniaAuthor Commented:
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

Open 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

Open 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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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)
0
holemaniaAuthor Commented:
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

Open in new window


If I can get just the first value it pulled, I am golden.  However that is a bit of a challenge.
0
slightwv (䄆 Netminder) Commented:
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?
1
PortletPaulEE Topic AdvisorCommented:
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.
0
holemaniaAuthor Commented:
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;

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Try this, using row_number() over()
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
            , row_number() over(partition by T.PART ORDER BY T.TRANS_DATE) rn
        FROM    TRANS_DETAIL T
        
        ) X ON X.MAIN_COMPONENT = W.PART AND X.rn=1
        
WHERE T.PART = 'VX112045'
GROUP BY  T.TRANS_ID, T.TRANS_DATE, T.PART, T.TASK, W.PART
ORDER BY T.TRANS_ID

Open in new window

the result from this is:
+---+---------+---------------------+---------------+-------+----------------+------------+
|   |   TRX   |      TRX_DATE       | SUB_COMPONENT |  JOB  | MAIN_COMPONENT | CUST_ORDER |
+---+---------+---------------------+---------------+-------+----------------+------------+
| 1 | 1661395 | 18.10.2010 00:00:00 | VX112045      | NULL  | NULL           | NULL       |
| 2 | 1661463 | 18.10.2010 00:00:00 | VX112045      | NULL  | NULL           | NULL       |
| 3 | 1661464 | 18.10.2010 00:00:00 | VX112045      | NULL  | NULL           | NULL       |
| 4 | 1686844 | 01.02.2011 00:00:00 | VX112045      | 12544 | 7788-0111      | 5739       |
| 5 | 1858703 | 12.06.2013 00:00:00 | VX112045      | NULL  | NULL           | NULL       |
| 6 | 1865792 | 15.07.2013 00:00:00 | VX112045      | 13766 | 998832-110     | 10967      |
+---+---------+---------------------+---------------+-------+----------------+------------+

Open in new window

The cust_order in row 4 (5739) differs to your query result (9468)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.