create a view

JOB Table
Name Null? Type
----------------------------------------- -------- ------------
JOBID NOT NULL NUMBER(4)
CLIENTID NOT NULL NUMBER(4)
REGONO VARCHAR2(6)
EMPLOYEEID NUMBER(4)
PICKUPTIME NOT NULL DATE
DELIVERYTIME NOT NULL DATE
PICKUPADDRESS NOT NULL VARCHAR2(50)
DELIVERYADDRESS NOT NULL VARCHAR2(50)
COMPLETED NOT NULL CHAR(3)
JOBDATE NOT NULL DATE

DRIVINGHISTORY Table
Name Null? Type
----------------------------------------- -------- ------------
HISTORYID NOT NULL NUMBER(4)
EMPLOYEEID NOT NULL NUMBER(4)
OFFENCENO NOT NULL VARCHAR2(10)
OFFENCETYPE NOT NULL VARCHAR2(20)
OFFENCEDATE NOT NULL DATE
DEMERITPOINT NOT NULL NUMBER(2)

Conditions:
Each row in the JOB table is a job. A set of jobs is a trip if it satisfies the following two requirements:

1. Every member of that set has the same CLIENTID, REGONO and PICKUPTIME, and REGNO is not null;
2. The jobs in the set can be ordered in a sequence so that for all jobs from the second to the second-last in the sequence, the DELIVERYADDRESS of one member of the sequence equals the PICKUPADDRESS of the following member of the sequence.

The PICKUPADDRESS of the first job in a trip sequence is called FIRST and the DELIVERYADDRESS of the final job in a trip sequence is called FINAL.

For each trip consisting of more than one job, list
CLIENTID, REGONO, PICKUPTIME, FIRST, FINAL and the sum of the
DEMERITPOINT of the drivers for the trip (give this column the title DEMERITS).

I'm to create a view called TRIPS which defines the multi-job trips.

I have difficulty with addressing how to label the trips as in how to address the delivery address of one to be the pickup address of another in the query.

Please help with some leads.

ratumeli,

Using Oracle 8i, I created the table:

create table jobs (
JOBID NUMBER(4) ,
CLIENTID NUMBER(4) ,
REGONO VARCHAR2(6) ,
EMPLOYEEID NUMBER(4) ,
PICKUPTIME DATE ,
DELIVERYTIME DATE ,
PICKUPADDRESS VARCHAR2(50) ,
DELIVERYADDRESS VARCHAR2(50) ,
COMPLETED CHAR(3) ,
JOBDATE DATE
);

insert into jobs values(1,1,'NY',1,sysdate,sysdate,'NY','NJ','N',sysdate);
insert into jobs values(1,1,'NY',1,sysdate,sysdate,'NJ','PA','N',sysdate);
insert into jobs values(1,1,'NY',1,sysdate,sysdate,'PA','CA','N',sysdate);

We can figure out the starting point of a trip (using a self-join and the minus set operation):


select x
from
(
select
a.pickupaddress x, a. deliveryaddress y
from
jobs a, jobs b
where
a.jobid = 1 and
a.deliveryaddress = b.pickupaddress
)

minus

select y
from
(
select
a.pickupaddress x, a. deliveryaddress y
from
jobs a, jobs b
where
a.jobid = 1 and
a.deliveryaddress = b.pickupaddress
);


Then, we can find the route using an hierarchal query:

select pickupaddress, deliveryaddress, level
from jobs
start with pickupaddress = 'NY'
connect by prior deliveryaddress = pickupaddress;

Cheers,
Dan

Hi Dan

I really appreciate your time and effor you've put in here. You've made this whole exercise more clearer for me.

Will give that a whirl.

Thanks
Ratu.