List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 31 1999 3:03pm
Subject:Re: Complicated Query
View as plain text  
Mike Machado wrote:
> 
> Ok, I have run into this mind boggling problem and hopefully someone out
> there with a fresh perspective can help out.
> 
> 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
> I am searching for, I have not figured out a way to match his login.
> Finding bob didn't seem to be a problem because he logged in after the
> first date I searched for. I need to have some query match both since
> john was logged during the hours I searched. I hope this is clear enough
> to get some answers. Thanks guys.
> 
> --
> Mike Machado

Hi Mike

Try:
SELECT 
	UserName 
FROM 
	table 
WHERE 
	(StopTime BETWEEN '1999-03-29 02:00:00' AND '1999-03-29 04:00:00)
	OR (StartTime BETWEEN '1999-03-29 02:00:00' AND '1999-03-29 04:00:00)
	OR (StartTime < '1999-03-29 02:00:00'
		AND StopTime > '1999-03-29 04:00:00')

Tschau
Christian


---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread1232@stripped

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@stripped instead.

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