MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old August 3rd, 2004, 08:17 AM
JPMac JPMac is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Posts: 12 JPMac User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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?

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Need help with nested sub query


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway
Stay green...Green IT