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
LVL 11
Wilder1626Asked:
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:
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 AdvisorCommented:
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.
Wilder1626Author Commented:
Yes, i need to produce a report and i don't want to repeat duplicated values like the Load Id.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
slightwv (䄆 Netminder) 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

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
Mark GeerlingsDatabase AdministratorCommented:
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
Wilder1626Author Commented:
Thanks a lot for your help. I'm good with both options.
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.