Re: challenging query

Assuming the end date of the location is the startdate of the next location of the personid, you can make a view as

select t1.personid, t1.location, t1.startdate, t2.startdate   from t1.visitlocation, t2.visitlocation   where
   t1.personid=t2.personid and t2.startdate =     (select min(startdate) from visitlocation where       personid=t1.personid and startdate>t1.startdate);

Lisa <lewis_at_mars-systems.com> wrote in article <7phng8$iec$1_at_usenet01.srv.cis.pitt.edu>...
: I have a visitlocation table that looks like
:
: personid varchar2(10);
: location varchar2(10);
: startdate date;
:
: I fill this table based on transactions that I get from another
system.
: This table keeps track of all of the locations that a person visited
and the
: start date/time of the visit. How would I query this table to find
all of
: the people (personid) that were in a particular location during a
particular
: time period (say 7/1/1999 - 7/15/1999). I also need to show an end
date for
: each location (based on the start date of next location) that the
person
: visited.
:
: I initially had an enddate in this table and had a stored procedure
to set
: the enddate, in real time as I received transactions from the another
: system, of the prior location whenever the individual moved to a new
: location. The problem is that it is possible that the system that is
: feeding my system, can send me transactions that are out of
transaction
: order or can send me duplicate transactions. When I took this
approach, my
: visitlocation table got totally screwed up.
:
: I was hoping someone could offer advice as to how to query the
visitlocation
: table if I only kept the startdate and my queries had to calculate
and
: display the enddate.
Received on Sat Aug 21 1999 - 04:10:38 CDT