Oracle - Count oads with duplicated stops in sequence

Hi

I have this table where it gives me all stops inside transport loads.
In this table i see:

1- The load IDs
2- The stop Number (Loads can have different number of stops)
3- You will stop the Stop Type, PK = PickUp and DL = Delivery
4- You will have the Location IDs for each stop inside the loads

SQL:
SELECT LOAD_ID,
  STOP_NUM,
  STOP_TYPE,
  LOCATION_ID,
  EARLY_ARRV
FROM LOAD_STOP 
order by LOAD_ID,  STOP_NUM;

Open in new window


What i need to do is to count the number of loads where i will see duplicated STORE_TYPE = DL and LOCATION_IDs that will follow in sequence that are Picked from the same location ID.

Ex:
Load stops in sequence
In above example, you see that STOP_NUM 2, 3, 4 is the same location ID 00002 but you also see that STOP_NUM 5 and 6 also are duplicated value for location id 00003.
Both locations 00002 and 00003 are PICKED (PK) at location ID 00001.

This would result to:

BOL COUNT = 1
FROM_ORIGIN: 00001
TO_DESTINATION: 00002

BOL COUNT = 1
FROM_ORIGIN: 00001
TO_DESTINATION: 00003

How can pull this query to count the number of loads where i have multiple time the same DL LOCATION_IDs in sequence from the same PICK (PK) location ID?

In attachment, you will have an example of the table and also a second sheet with the expected results.

Any help would be more than appreciated.

Thanks for your help
Copy-of-sql_load_stop.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.

awking00Information Technology SpecialistCommented:
Assuming the second sheet (result) shows only results for load_id load2, but should also include results for load_id load3 -

select dl.load_id,dl.cnt as bol_count, pk.location_id as from_origin, dl.location_id as to_destination from
(select load_id, location_id
 from load_stop
 where stop_type= 'PK') pk,
(select load_id, location_id, count(*) - 1 cnt
 from load_stop
 where stop_type = 'DL'
 group by load_id, location_id
 having count(*) > 1) dl
where pk.load_id = dl.load_id
order by dl.load_id, dl.location_id;
flow01Commented:
I can't figure out if  you need the results by load_id or in total. And your results sheet does not seem to match your data.

Try next query and comment

with stops as
(
select 'LOAD1' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 3 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 4 stopnum, 'DL' stoptype, '00004' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 3 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 4 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 5 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 6 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 3 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 3 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 4 stopnum, 'DL' stoptype, '00004' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 5 stopnum, 'DL' stoptype, '00005' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD6' load_id, 1 stopnum, 'PK' stoptype, '00008' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD6' load_id, 2 stopnum, 'DL' stoptype, '00009' location_id,  '12-06-15' early_arrv from dual
)
, loads
as
(select load_id, stopnum, location_id from stops where stoptype = 'PK'
)
, deliveries
as
(select load_id, stopnum, location_id , lag(stopnum) over (partition by load_id order by stopnum) previous_stopnum
from stops where stoptype = 'DL'
)
, sum_deliveries
as
(
select load_Id, min(stopnum) first_delivery, location_id, count(*)  bol_count
from deliveries
where (previous_stopnum is null
       or
       previous_stopnum = stopnum - 1)
group by load_Id, location_id
)
select sum_deliveries.load_id, bol_count, loads.location_id  from_origin,  sum_deliveries.location_id to_destination
from sum_deliveries,
loads
where loads.load_id = sum_deliveries.load_id
order by sum_deliveries.load_id, sum_deliveries.location_id
/
Wilder1626Author Commented:
Ok, i've tried both query's and i think we are getting very close.

The query will not pull per loads but per totals origin destination combination.  

I need to only see how many loads exist where a specific PK delivers to multiple same DL locationI_ID inside the same load.

So in load2 of my first post, even if i have 3 times 00002 and 2 times 00003 coming out of 00001, this is just telling that on 1 load, i have 2 instant where i see duplicated PK to same DL store.

One for:
LOAD_COUNT = 1
FROM_ORIGIN: 00001
TO_DESTINATION: 00002

Another for:
LOAD_COUNT = 1
FROM_ORIGIN: 00001
TO_DESTINATION: 00003

If i have another load with the exact same PKs and DLs, that would make:

LOAD_COUNT = 2
FROM_ORIGIN: 00001
TO_DESTINATION: 00002

LOAD_COUNT = 2
FROM_ORIGIN: 00001
TO_DESTINATION: 00003

Hope this will help!!
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!

flow01Commented:
with stops as
(
select 'LOAD1' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 3 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD1' load_id, 4 stopnum, 'DL' stoptype, '00004' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 3 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 4 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 5 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD2' load_id, 6 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD3' load_id, 3 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 1 stopnum, 'PK' stoptype, '00001' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 2 stopnum, 'DL' stoptype, '00002' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 3 stopnum, 'DL' stoptype, '00003' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 4 stopnum, 'DL' stoptype, '00004' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD4' load_id, 5 stopnum, 'DL' stoptype, '00005' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD6' load_id, 1 stopnum, 'PK' stoptype, '00008' location_id,  '12-06-15' early_arrv from dual union
select 'LOAD6' load_id, 2 stopnum, 'DL' stoptype, '00009' location_id,  '12-06-15' early_arrv from dual
)
, loads
as
(select load_id, stopnum, location_id from stops where stoptype = 'PK'
)
, deliveries
as
(select load_id, stopnum, location_id , lag(stopnum) over (partition by load_id order by stopnum) previous_stopnum
from stops where stoptype = 'DL'
)
, sum_deliveries
as
(
select load_Id, min(stopnum) first_delivery, location_id, count(*)  bol_count
from deliveries
where (previous_stopnum is null
       or
       previous_stopnum = stopnum - 1)
group by load_Id, location_id
)
, origin_destionations
as
(
select sum_deliveries.load_id, bol_count, loads.location_id  from_origin,  sum_deliveries.location_id to_destination
from sum_deliveries,
loads
where loads.load_id = sum_deliveries.load_id
)
select count(*) bol_count, from_origin, to_destination
from origin_destionations
group by from_origin, to_destination
order by from_origin, to_destination
/
slightwv (䄆 Netminder) Commented:
Here is my attempt.  It should only hit the table once.

I figure it can be cleaned up a little to remove some nested selects and I'll try to get some time later to try.

/*
drop table tab1 purge;
create table tab1(load_id char(5), stop_num number, stop_type char(2), location_id char(5));

insert into tab1 values('LOAD1',1,'PK','00001');
insert into tab1 values('LOAD1',2,'DL','00002');
insert into tab1 values('LOAD1',3,'DL','00003');
insert into tab1 values('LOAD1',4,'DL','00004');

insert into tab1 values('LOAD2',1,'PK','00001');
insert into tab1 values('LOAD2',2,'DL','00002');
insert into tab1 values('LOAD2',3,'DL','00002');
insert into tab1 values('LOAD2',4,'DL','00002');
insert into tab1 values('LOAD2',5,'DL','00003');
insert into tab1 values('LOAD2',6,'DL','00003');

insert into tab1 values('LOAD3',1,'PK','00001');
insert into tab1 values('LOAD3',2,'DL','00002');
insert into tab1 values('LOAD3',3,'DL','00002');

insert into tab1 values('LOAD4',1,'PK','00001');
insert into tab1 values('LOAD4',2,'DL','00002');
insert into tab1 values('LOAD4',3,'DL','00003');
insert into tab1 values('LOAD4',4,'DL','00004');
insert into tab1 values('LOAD4',5,'DL','00005');

insert into tab1 values('LOAD6',1,'PK','00008');
insert into tab1 values('LOAD6',2,'DL','00009');
commit;
*/

select load_id, from_origin, to_origin, dl_count
from (
	select load_id, max(from_origin) over(partition by load_id) from_origin, to_origin, dl_count
	from (
		select load_id,
			case when stop_type='PK' then location_id end from_origin,
			case when stop_type='DL' then location_id end to_origin,
			dl_count
		from (
			select distinct load_id, stop_type, location_id, dl_count
			from (
				select load_id, stop_type, location_id, dl_count, max(dl_count) over(partition by load_id order by load_id) max_dl_count
				from (
					select load_id, stop_type, location_id,
						count(stop_type) over(partition by load_id, location_id order by load_id, location_id) dl_count
					from tab1
				)
			)
			where max_dl_count>1
		)
	)
)
where to_origin is not null
order by 1,2
/

Open in new window

awking00Information Technology SpecialistCommented:
I guess I'm still a little confused. Are you saying that the output should be like
bol_count from_origin to_destination
2                       00001          00002
1                       00001          00003
because, for the first record,  there are two load steps that have a pickup of 00001 and multiple destinations of 00002, and the second record shows 1 load step with a pickup of 00001 and multiple destinations of 00003?
Wilder1626Author Commented:
Question:
Are you saying that the output should be like
bol_count from_origin to_destination
2                       00001          00002
1                       00001          00003

Answer: Correct.


Question:
because, for the first record,  there are two load steps that have a pickup of 00001 and multiple destinations of 00002, and the second record shows 1 load step with a pickup of 00001 and multiple destinations of 00003?

Answer: This is correct.
slightwv (䄆 Netminder) Commented:
Try this update to my test case above:
select from_origin, to_origin, count(*)
from (
	select load_id, max(from_origin) over(partition by load_id) from_origin, to_origin, dl_count
	from (
		select load_id,
			case when stop_type='PK' then location_id end from_origin,
			case when stop_type='DL' then location_id end to_origin,
			dl_count
		from (
			select distinct load_id, stop_type, location_id, dl_count
			from (
				select load_id, stop_type, location_id, dl_count, max(dl_count) over(partition by load_id order by load_id) max_dl_count
				from (
					select load_id, stop_type, location_id,
						count(stop_type) over(partition by load_id, location_id order by load_id, location_id) dl_count
					from tab1
				)
			)
			where max_dl_count>1
		)
	)
)
where to_origin is not null
group by from_origin, to_origin
order by 1,2
/

Open in new window

Wilder1626Author Commented:
I like the result. this is pulling the information correctly.
slightwv (䄆 Netminder) Commented:
Now that I think I understand, I'm working on cleaning it up.
slightwv (䄆 Netminder) Commented:
The dangers out starting down one path is it is hard to back up and go down a different path.

This one is a little cleaner but still not as good as I think it can be.

I'll try to get some more time this evening to play around with it.

select from_origin, to_origin, count(*)
from (
	select load_id, max(from_origin) over(partition by load_id) from_origin, to_origin, dl_count
	from (
		select load_id,
			case when stop_type='PK' then location_id end from_origin,
			case when stop_type='DL' then location_id end to_origin,
			dl_count
		from (
				select load_id, stop_type, location_id, dl_count
				from (
					select load_id, stop_type, location_id,
						max(count(*)) over(partition by load_id) dl_count
					from tab1
					group by load_id, stop_type, location_id
				)
				where dl_count>1
		)
	)
)
where to_origin is not null
group by from_origin, to_origin
order by 1,2
/

Open in new window

sdstuberCommented:
this returns the expected results given the sample data
I made assumption that the first stop in a load was the pickup and all others were drops.
If that is an invalid assumption please post some sample data that demonstrates other combinations and then the expected results for those combinations.

  SELECT from_origin, to_origin, COUNT(DISTINCT load_id)
    FROM (SELECT load_id,
                 from_origin,
                 location_id to_origin,
                 COUNT(*) OVER (PARTITION BY load_id, from_origin, location_id) cnt
            FROM (SELECT t.*,
                         FIRST_VALUE(location_id) OVER(PARTITION BY load_id ORDER BY stop_num)
                             from_origin
                    FROM tab1 t))
   WHERE cnt > 1
GROUP BY from_origin, to_origin
ORDER BY from_origin, to_origin

Open in new window


if your version doesn't support FIRST_VALUE then replace
FIRST_VALUE(location_id) OVER(PARTITION BY load_id ORDER BY stop_num)
with
MIN(CASE WHEN stop_num = 1 THEN location_id END) OVER (PARTITION BY load_id)

and leave the rest of the query as is
Wilder1626Author Commented:
Hi sdstuber

So far, it looks like you are pretty close.

I just found a load scenario that looks like below saying that some time, what comes after the first stop are not always just deliveries.

In below example, even if you have 3 times the same location 00009 in the load 6, 2 are coming from 00008 and only 1 comes from 00001.
load_in_sequence_no2
In this case, it will only pull me the 00008 to 00009 as a duplicate.

Please see in attachment the updated LOAD 6 with this scenario and the result needed.
Copy-of-sql_load_stop_no2.xlsx
sdstuberCommented:
Is it always going to be true pickups will be at the beginning? maybe on, maybe multiple as in your new example

or, can you have a load  pickup, drop off, pickup and drop off again?
Wilder1626Author Commented:
Correct, I can have a load  pickup, drop off, pickup and drop off again.

Normally, when i pickup something, i need to drop everything before i pickup again and drop again.
sdstuberCommented:
If you drop everything off before picking up again then the 3rd 00009 stop of LOAD6 might be incomplete because you would need to stop at 00001 first.

The schedule indicates that, but it doesn't mean you can simply move all all the stops together, they need to take into account the pickups.

I can still do the aggregation that way you have shown if you want, but I wanted to make sure that you wanted to aggregate by LOAD and not by pickup.

And, if we are aggregating by LOAD,  why is the start origin 00008 and not 00001  for 00009,  Is it because 00008 came first, or is it because 00008 had more pickup stops then 00001?
sdstuberCommented:
Maybe I'm misunderstanding between the text and the highlighting.  I was assuming the highlighting was to indicate groups that should be aggregated.  But the text and xls result seem to indicate the opposite.
sdstuberCommented:
I think I have it figured out.  I was over thinking the colorcoding/highlighting.

But I do need clarification on the how the origin is picked.

Lets say I have load7 with 3 pickups from different locations (1,2,3) or possibly 00001 and then two pickups at 00002 and then 5 delivieries, with some of them being duplicates.

What is the origin for those duplicate drop off locations in each of those scenarios?

What if in LOAD6 I had two pickups at 00001 before continuing on to 00009 for the third time, would that change the expected results? If so, how?

What if pickups at the same location are not sequential? Can that happen? If so, how does that change the results?
Wilder1626Author Commented:
If i understand your first question, i will never get 3 picks and deliver all at the end.


What if in LOAD6 I had to pickups at 00001 before continuing on to 00009 for the third time, would that change the expected results?

this is a very good question.

If i have Pick 00001 deliver 00002, pick again at 00001 and deliver to 00002, that would be a duplication.
sdstuberCommented:
If I assume the first stop of a load is a pickup and that pickup becomes the origin for all deliveries on that load regardless of subsequent pickups then this returns the expected results given the sample data.

If my assumptions are not correct please elaborate and expand the sample data.


 
 SELECT COUNT(DISTINCT load_id) bol_count, from_origin, to_destination
    FROM (SELECT *
            FROM (SELECT load_id,
                         FIRST_VALUE(location_id) OVER(PARTITION BY load_id ORDER BY stop_num)
                             from_origin,
                         location_id to_destination,
                         COUNT(CASE WHEN stop_type = 'DL' THEN 1 END)
                             OVER (PARTITION BY load_id, location_id)
                             cnt
                    FROM tab1)
           WHERE cnt > 1)
GROUP BY from_origin, to_destination
ORDER BY from_origin, to_destination;

Open in new window

sdstuberCommented:
>>>If i have Pick 00001 deliver 00002, pick again at 00001 and deliver to 00002, that would be a duplication.

ok, that negates my last answer

and sort of answers my other question of what if pickups at the same location are not sequential.

So - if a pickup only hapens once during a load, then the first pickup defines the origin for duplications?
But if a pickup occurs more than once during a load then it becomes the origin for deliveries that follow?

Is that correct?
sdstuberCommented:
what if the pickups are staggered?

PK 1
DL 2
PK 3
DL 4
PK 1
DL 5
PK 3
DL 6
DL 2
DL 4

Can that happen? If so, then what are the expected results?
Wilder1626Author Commented:
let me check something.

You have pretty good questions here.
sdstuberCommented:
I'm going to take a guess at the rule...

If you have a duplicate delivery location, the origin is the most recent pickup prior to the first occurrence of the delivery location.

So, in my last example  DL 2 would have origin PK 1 and DL 4 would origin PK 3

Does that sound right?

Or, how about for each duplicate delivery location, the origin is the first pickup prior to the first occurrence of the delivery location.

My previous example would produce the same results with this rule too.

Does that sound right?
Wilder1626Author Commented:
HI

To you statement: If you have a duplicate delivery location, the origin is the most recent pickup prior to the first occurrence of the delivery location.

You are absolutely right!!!!!  DL 2 would have origin PK 1 and DL 4 would origin PK 3
sdstuberCommented:
ok, that's good, but don't base your judgement on the results of my small example,  the second guess is a different rule but for that data produces the same result.
sdstuberCommented:
if this is correct:

If you have a duplicate delivery location, the origin is the most recent pickup prior to the first occurrence of the delivery location.

Then this seems to work, unfortunately it does require a double hit on the table


  SELECT COUNT(DISTINCT dl.load_id) bol_count, pk.location_id from_orgin, dl.location_id to_destination
    FROM (SELECT x.*, MIN(recent_pk) OVER (PARTITION BY load_id, location_id) first_pk
            FROM (SELECT tab1.*,
                         LAST_VALUE(CASE WHEN stop_type = 'PK' THEN stop_num END IGNORE NULLS)
                         OVER(PARTITION BY load_id ORDER BY stop_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                             recent_pk,
                         COUNT(CASE WHEN stop_type = 'DL' THEN 1 END) OVER (PARTITION BY load_id, location_id) cnt
                    FROM tab1) x
           WHERE stop_type = 'DL' AND cnt > 1) dl,
         (SELECT *
            FROM tab1
           WHERE stop_type = 'PK') pk
   WHERE dl.load_id = pk.load_id AND dl.first_pk = pk.stop_num
GROUP BY pk.location_id, dl.location_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
Wilder1626Author Commented:
Thanks for all your helps, this last one does 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.