select
(select count(*) from inbound as t1
where nsew='IGNITION ON'
and t1.dt < t2.dt
and t1.vehicleid = t2.vehicleid
) as ignitionGroup,
vehicleid, count(*)
from inbound as t2
where
t2.nsew='IDLE'
group by vehicleid, ignitionGroup
--- John mccosker <[EMAIL PROTECTED]> wrote:
> Hi Isaac,
>
> I modified the query to this,
>
> SELECT COUNT(NSEW) AS CT
> FROM INBOUND AS Q1
> WHERE DATEDIFF(DAY,Q1.DT,GETDATE())=7 AND Q1.NSEW =
> 'IDLE' AND Q1.VEHICLEID=137
> AND Q1.DT >= (SELECT MAX(Q2.DT)
> FROM INBOUND AS Q2
> WHERE DATEDIFF(DAY,Q2.DT,GETDATE())=7 AND
> Q2.NSEW = 'IGNITION ON' AND Q2.VEHICLEID=137
> )
> AND Q1.DT <= (SELECT MAX(Q3.DT)
> FROM INBOUND AS Q3
> WHERE DATEDIFF(DAY,Q3.DT,GETDATE())=7 AND
> Q3.NSEW = 'IGNITION OFF' AND Q3.VEHICLEID=137
> )
>
> however it will not pick up on a more complicated
> pattern like this,
>
> DT VEHICLEID NSEW
> 03/08/2004 08:30:16 169 IGNITION ON
> 03/08/2004 08:35:33 169 IDLE
> 03/08/2004 08:40:17 169 IDLE
> 03/08/2004 08:45:51 169 IDLE
> 03/08/2004 08:50:23 169 IDLE
> 03/08/2004 08:55:29 169 IDLE
> 03/08/2004 09:00:16 169 IGNITION OFF
> 03/08/2004 09:05:16 169 stopped
> 03/08/2004 09:30:16 169 IGNITION ON
> 03/08/2004 09:35:33 169 IDLE
> 03/08/2004 09:40:17 169 IDLE
> 03/08/2004 09:45:51 169 IDLE
> 03/08/2004 10:00:16 169 IGNITION OFF
>
> it will always count the last block giving a result
> of,
> 3, instead of a result of,
> 5
> 3
>
> Q0.DIRECTION is actually Q0.NSEW, i aplogise, a type
> error,
> Q0.NSEW is a CHAR datatype
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
