Oracle - Find empty miles from 2 tables

Hi

I have 2 tables.

Table NETWORK:
network table
Table LOAD_STOP:
Load_stop table
What i need to do is to find what is the empty miles between 2 locations.

To pull the empty miles, the logic will be:
find in the load stop table the Last Stop_Type DL Location_ID to Last Stop_Type CL Location_ID distance from Network Table.

If we take the example above, the last location ID with Stop_Type DL is: 00217 and the last Stop_Type with CL is: 03_DOM.

Once we know the location 1 and 2, it will look for this combination in the Network table when( LOCATION_ID1 = 00217 and  LOCATION_ID2 = 03_DOM) or (LOCATION_ID1 = 03_DOM and  LOCATION_ID2 = 00217) and get the MILES value, in this case 170.
query result
How can i do that.

I'm joining the 2 table extract.

Thanks for your help
LOAD-DETAIL.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.

johnsoneSenior Oracle DBACommented:
This will give you the result that you asked for, but I have a feeling there is more to this than what you asked.
SELECT miles empty_miles 
FROM   network a, 
       (SELECT location_id 
        FROM   (SELECT location_id, 
                       Row_number() 
                         over ( 
                           ORDER BY stop_num DESC) rn 
                FROM   load_stop 
                WHERE  stop_type = 'DL') 
        WHERE  rn = 1) b, 
       (SELECT location_id 
        FROM   (SELECT location_id, 
                       Row_number() 
                         over ( 
                           ORDER BY stop_num DESC) rn 
                FROM   load_stop 
                WHERE  stop_type = 'CL') 
        WHERE  rn = 1) c 
WHERE  ( a.location_id1 = b.location_id 
         AND a.location_id2 = c.location_id ) 
        OR ( a.location_id1 = c.location_id 
             AND a.location_id2 = b.location_id ); 

Open in new window

Wilder1626Author Commented:
Hi, you are absolutely right. I forgot to add the the LOAD_ID column in the LOAD_STOP table.

I may have multiple LOAD_IDs and i would like to pull the empty miles per LOAD_ID's.

I totally forgot it. really sorry.

the final results should be the LOAD_ID, EMPTY_MILES

ex:
LOAD_ID | EMPTY_MILE
LOAD1     | 170
Wilder1626Author Commented:
Here is an updated extract of the tables.

sorry again.
LOAD-DETAIL-NO2.xlsx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I think there is an issue in your updated data.

In the test below I changed:
insert into my_load_stop values('LOAD2','03_DOM','CL',4);

to:
insert into my_load_stop values('LOAD2','00217','CL',4);


This otherwise I don't see how LOAD2 would return a value.

Here is my setup and results:
/*
drop table my_load_stop purge;
create table my_load_stop(LOAD_ID varchar2(5), LOCATION_ID varchar2(6), STOP_TYPE varchar2(2), STOP_NUM number); 

insert into my_load_stop values('LOAD1','03_DOM','SU',1);
insert into my_load_stop values('LOAD1','03','PK',2);
insert into my_load_stop values('LOAD1','07801','DL',3);
insert into my_load_stop values('LOAD1','00217','DL',4);
insert into my_load_stop values('LOAD1','03_DOM','CL',5);
--
insert into my_load_stop values('LOAD2','03_DOM','SU',1);
insert into my_load_stop values('LOAD2','03','PK',2);
insert into my_load_stop values('LOAD2','07801','DL',3);
insert into my_load_stop values('LOAD2','00217','CL',4);
commit;

drop table my_network purge;
create table my_network(LOCATION_ID1 varchar2(6), LOCATION_ID2 varchar2(6), MILES number, DRIVE_TIME varchar2(6));

insert into my_network values('00217','03_DOM',170,'000:00');
insert into my_network values('03_DOM','03',1,'000:00');
insert into my_network values('03','07801',174,'000:00');
insert into my_network values('07801','00217',6,'000:00');
commit;

*/

select load_id, miles from
(
select load_id, max(CL_LOCATION) cl_loc, max(DL_LOCATION) dl_loc
from (
	select	load_id,
	case when stop_num = max(case when stop_type = 'CL' then stop_num end) over(partition by load_id) then location_id end CL_LOCATION,
	case when stop_num = max(case when stop_type = 'DL' then stop_num end) over(partition by load_id) then location_id end DL_LOCATION
	from my_load_stop
)
group by load_id
) ls,
my_network n
where
	n.location_id1 in (ls.cl_loc,dl_loc)
	and
	n.location_id2 in (ls.cl_loc,dl_loc)
/

Open in new window


My results:
LOAD_      MILES
----- ----------
LOAD1        170
LOAD2          6

Open in new window

Wilder1626Author Commented:
Normally, i should get:
Load1      170
Load2      -

Load1 being from 00217 to 03_DOM at 170 (In the Network Table)
Load2 being from 07801 to 03_DOM or 03_DOM to 07801 at NULL since it does not exist in the network table.
Wilder1626Author Commented:
If there is no match in the network table, the empty miles should be ZERO by default.

So it would end with:
Load1      170
Load2      0

I think we hare very close to the final results needed.
slightwv (䄆 Netminder) Commented:
Try this:
select load_id, nvl(miles,0) miles from
(
select load_id, max(CL_LOCATION) cl_loc, max(DL_LOCATION) dl_loc
from (
	select	load_id,
	case when stop_num = max(case when stop_type = 'CL' then stop_num end) over(partition by load_id) then location_id end CL_LOCATION,
	case when stop_num = max(case when stop_type = 'DL' then stop_num end) over(partition by load_id) then location_id end DL_LOCATION
	from my_load_stop
)
group by load_id
) ls left outer join my_network n
on
	n.location_id1 in (ls.cl_loc,dl_loc)
	and
	n.location_id2 in (ls.cl_loc,dl_loc)
/

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
Wilder1626Author Commented:
Thanks for your help. I'm pulling exactly what i needed.
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.