List:General Discussion« Previous MessageNext Message »
From:Wayne Cease Date:March 31 1999 3:02pm
Subject:Re: Complicated Query
View as plain text  
Christian Mack wrote:
> > I have a table with 'UserName varchar(15)', 'StartTime datetime', and
> > 'StopTime datetime'. This table keeps track of every login to a system
> > I am running.
> >
> > Now, I want to be able to throw a range and see who was logged during
> > that range. It is not to hard to get a list of the people who logged in
> > or out BETWEEN the times I specify, but lets try an example of the
> > problem I have come up against:
> >
> > here are a few sample entries
> > 'john','1999-03-29 01:00:00','1999-03-29 06:00:00'
> > 'bob','1999-03-29 02:30:00','1999-03-29 03:30:00'
> > 'bill','1999-03-29 06:00:00','1999-03-29 08:00:00'
> >
> > Now I want to know everyone who was logged in between '1999-03-29
> > 02:00:00' and '1999-03-29 04:00:00'
> >
> > So this should obviously give me john and bob but not bill. The
> > problems I have come across are since john logged BEFORE the first date

The following SQL should get you everything you need.  END_TIME and
BEGIN_TIME are variables; in your example above END_TIME = '1999-03-29
04:00:00' and BEGIN_TIME = '1999-03-29 02:00:00'.


SELECT
  UserName
FROM
  table
WHERE
      StartTime <= END_TIME
  AND StopTime >= BEGIN_TIME;



-- 
Wayne Cease
wayne@stripped

Phone: (404) 277-0544
Fax:   (770) 801-1646
Thread
Complicated QueryMike Machado30 Mar
  • Re: Complicated QueryChristian Mack31 Mar
  • Re: Complicated QueryWayne Cease31 Mar
Re: Complicated QueryFred Lindberg30 Mar
Re: Complicated Query("Christian Mack")31 Mar