Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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?
So here's my query:
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.
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?
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'
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>>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.
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
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.
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.
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.
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
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
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)
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.
If I can get just the first value it pulled, I am golden. However that is a bit of a challenge.
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
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?
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.
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;
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
+---+---------+---------------------+---------------+-------+----------------+------------+
| | 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 |
+---+---------+---------------------+---------------+-------+----------------+------------+
Premium Content
You need an Expert Office subscription to comment.Start Free Trial