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

Oracle DatabaseSQL

Last Comment

Wilder1626

8/22/2022 - Mon

slightwv (䄆 Netminder)

8/2/2015

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

PortletPaul

8/2/2015

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.

Wilder1626

8/2/2015

ASKER

Yes, i need to produce a report and i don't want to repeat duplicated values like the Load Id.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

8/2/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.