need help with nested subquery
From: JPMac (JPMac_at_discussions.microsoft.com)
Date: 08/03/04
- Next message: Kristoffer Persson: "Stored procedure script problem"
- Previous message: User: "Re: INSERT INTO with ORDER BY clause in the SELECT"
- Next in thread: Anith Sen: "Re: need help with nested subquery"
- Reply: Anith Sen: "Re: need help with nested subquery"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 3 Aug 2004 07:15:02 -0700
hi,
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
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
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 i
gnition 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 COUNT(NSEW) AS CT
FROM INBOUND AS Q1
WHERE DATEDIFF(DAY,Q1.DT,GETDATE())=7 AND Q1.NSEW = 'IDLE' AND Q1.VEH
ICLEID=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
)
it will always count the last block giving a result of,
3, instead of a result of,
5
3
- Next message: Kristoffer Persson: "Stored procedure script problem"
- Previous message: User: "Re: INSERT INTO with ORDER BY clause in the SELECT"
- Next in thread: Anith Sen: "Re: need help with nested subquery"
- Reply: Anith Sen: "Re: need help with nested subquery"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
