* [EMAIL PROTECTED]
> Hi, I woud like to ask for help with sql select.
> I have table -> visitor, arrival, departure. (Time is in timestamp
> in sec. from 1970) I need select top of concurrently present
> visitors, between 00.00 and 29.59. So my boss want know when was
> shop most full and how many wisitors was there. May be other thing
> which would by nice is select top of concurrently present visitors
> every 5 minutes like format 00.05 4, 00.10 5, 00.15 8 ,00.20 6 ...
> 23.55 7. Thanks for any idea Beld
Maybe something like this...:
use test;
create table visit (id int primary key not null,arrival int,departure int);
insert into visit values
(1,1,3),(2,2,4),(3,2,5),(4,2,3),(5,3,6),
(6,3,4),(7,3,4),(8,4,5),(9,5,6);
create temporary table times (id int primary key not null);
insert into times values (1),(2),(3),(4),(5),(6);
select times.id,count(*) as cnt
from times,visit
where
times.id between visit.arrival and visit.departure
group by times.id;
In the 'times' table, you would put the timestamps you want as checkpoints,
00:05, 00:10, 00:15 and so on for the appropriate date.
Add ORDER BY to get the most busy times first:
... order by cnt desc;
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
