|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Need help with nested sub query
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 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 IdleCount.ct FROM ( 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 Q2.DT < Q1.DT ) AND Q1.DT <= (SELECT MIN(Q3.DT) FROM INBOUND AS Q3 WHERE DATEDIFF(DAY,Q3.DT,GETDATE())=7 AND Q3.NSEW = 'IGNITION OFF' AND Q3.VEHICLEID=137 AND Q3.DT > Q1.DT ) ) IdleCount however the results coming back are not what is expected, concluding that this sql statement is not right. Do any guru's have any ideas? |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Need help with nested sub query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|