I have a recordset like so,
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
what I am trying to do is count the idle instances between an ignition on and an ignition off instance. I also want to work out the duration in time between the first idle instance after an ignition on and the ignition off.
However I thought I would leave this until I find out where I am going wrong with the first part of my query,
this is the first part of the query (count the idle instances between an ON and OFF),
SELECT DT, VEHICLEID,NSEW,
( SELECT COUNT(NSEW)AS DCT
FROM INBOUND AS Q1
WHERE DATEDIFF(DAY,Q0.DT,GETDATE())=7 AND Q0.VEHICLEID=137 AND Q0.NSEW = 'IDLE'
AND Q0.DT >
( SELECT MIN(Q2.DT)
FROM INBOUND AS Q2
WHERE Q0.DIRECTION = 'IGNITION ON'
)
AND Q0.DT <
( SELECT MAX(Q3.DT)
FROM INBOUND AS Q3
WHERE Q0.DIRECTION = 'IGNITION OFF'
)
)
FROM INBOUND AS Q0
the error message is
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
I tried casting the count aggregate, although I suspect it may have something to do with the DT, VEHICLEID,NSEW, included in the top level query.
Thanks for any help,
John.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
