Oracle query load stop with Min early_delivery and Max early_delivery

Wilder1626
Wilder1626 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Looks like a simple join with a group?

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
/

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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)
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

Open in new window

Are you producing a report? if so most report products have ways to output the way you proposed in the spreadsheet.
Yes, i need to produce a report and i don't want to repeat duplicated values like the Load Id.
Ensure you’re charging the right price for your IT

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!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
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:
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
)
/

Open in new window

Mark GeerlingsDatabase Administrator
Commented:
I would run this command first in SQL*PLUS:
break on dest_id

Then run the query like this:

select ol.load_id "ID", dest_id, min(early_del) "EarlyDel",
max(late_del) "LateDel"
from ord o join ord_load ol on o.id=ol.ord_id
group by ol.load_id, dest_id
order by ol.load_id, dest_id
Thanks a lot for your help. I'm good with both options.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial