July 9, 2004 at 9:22 am
| i have the table SendController (controllerID int, date datetime, regionID int, mission ntext) i want to select all controllers that didn't go to the same region 10 times consecutively. How to do that??? |
July 9, 2004 at 10:08 am
select a.controllerID
from SendController a
inner join (select distinct regionID, count(regionID) as RegionNo FROM SendController GROUP BY regionID) b
on a.regionID = b.regionID
and b.RegionNo 10
July 12, 2004 at 12:36 am
First, controller ID is the primary key so the join must be on controller ID. Suppose that is done, what about the date? 10 times consecutively, i mean by that the date. Maybe the controller went 10 times but not successively. The query would be wrong. How do i specify that 10 dates consecutively?> |
July 12, 2004 at 9:33 pm
Can you be a little more specific? What do you mean by ten times consecutively?
1. That it went 10 in the same date
2. That the region is visited 10 times before another is visited?
3. Can a controller go to more than one region at a time?
* Noel
July 12, 2004 at 10:59 pm
Select ControlID from SendController
Group By ControlID,Datetime
having count(controlid) <10
One assumtion here is the datetime table does not have time embedded in them.
July 13, 2004 at 12:33 am
| What i mean by 10 times consecutively is that a certain controller shouldn't be sent to the same region 10 times before another region is visited |
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply