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]
