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?
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

0
PortletPaulfreelancerCommented:
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.
0
Wilder1626Author Commented:
Yes, i need to produce a report and i don't want to repeat duplicated values like the Load Id.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.