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 query load stop with Min early_delivery and Max early_delivery
Hi,
I have 2 tables:
- Ord
- Ord_Load
I need to be able to create a report per LOAD_ID from ORD_LOAD table and per DEST_ID from ORD table.
Each DEST_ID can have multiple orders in ID column and each ID has an early_del and a late_del date and time.
I need to pull per load ID and destination ID what is the minimum early_del and the maximun late_del date and time among the orders from each stops
How can I do this?
You will find in attachment the 2 tables + what should be the final result.
Thanks for your help.
Table-extract-and-final-result.xlsx
I have 2 tables:
- Ord
- Ord_Load
I need to be able to create a report per LOAD_ID from ORD_LOAD table and per DEST_ID from ORD table.
Each DEST_ID can have multiple orders in ID column and each ID has an early_del and a late_del date and time.
I need to pull per load ID and destination ID what is the minimum early_del and the maximun late_del date and time among the orders from each stops
How can I do this?
You will find in attachment the 2 tables + what should be the final result.
Thanks for your help.
Table-extract-and-final-result.xlsx
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Looks like a simple join with a group?
Try this:
Try this:
select ol.load_id, dest_id, min(early_del),max(late_del)
from ord o join ord_load ol on o.id=ol.ord_id
group by ol.load_id, dest_id
/
yes, it does look exactly like that join and group query
the only difference to expected result is that the LOAD_ID will repeat on each row (& this is normal behaviour for SQL results)
the only difference to expected result is that the LOAD_ID will repeat on each row (& this is normal behaviour for SQL results)
LOAD_ID DEST_ID EARLY_DEL LATE_DEL
LOAD1 00317 2015-06-22 2015-06-22
LOAD1 00346 2015-06-22 2015-06-22
LOAD1 00368 2015-06-22 2015-06-22
LOAD1 00373 2015-06-22 2015-06-22
LOAD1 00385 2015-06-22 2015-06-22
LOAD1 02702 2015-06-22 2015-06-22
What tool are you using?
If sqlplus, you can emulate what you want. Just pick the order columns and add the correct ones to the ORDER BY in the ROW_NUMBER call:
If sqlplus, you can emulate what you want. Just pick the order columns and add the correct ones to the ORDER BY in the ROW_NUMBER call:
select case when rn=1 then load_id end load_id, dest_id, early_del,late_del
from (
select ol.load_id, dest_id, min(early_del) early_del,max(late_del) late_del,
row_number() over(order by dest_id) rn
from myord o join myord_load ol on o.id=ol.ord_id
group by ol.load_id, dest_id
)
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial