Wilder1626
asked on
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:
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:
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
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;
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:
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
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
/
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
from sum_deliveries,
loads
where loads.load_id = sum_deliveries.load_id
order by sum_deliveries.load_id, sum_deliveries.location_id
/
ASKER
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!!
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!!
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
/
(
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
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
/
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.
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
/
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?
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?
ASKER
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.
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.
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
/
ASKER
I like the result. this is pulling the information correctly.
Now that I think I understand, I'm working on cleaning it up.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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
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
ASKER
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.
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
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.
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
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?
or, can you have a load pickup, drop off, pickup and drop off again?
ASKER
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.
Normally, when i pickup something, i need to drop everything before i pickup again and drop again.
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?
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?
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.
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?
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?
ASKER
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.
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.
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.
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;
>>>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?
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?
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?
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?
ASKER
let me check something.
You have pretty good questions here.
You have pretty good questions here.
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?
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?
ASKER
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your helps, this last one does exactly what i needed.
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;