asked on

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

slightwv (䄆 Netminder)

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

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

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION