Count one event regardless of duration

[jbr87]

Hi guys,



A little bit of background here I'm doing some research looking into the frequency of stopping events in a wind farm. I have 4 columns that I am interested in one being a timestamp (this is the one that is causing me trouble) Another being the tower ID# and the two others being windspeed and tower rpm.

What I want to do is count the number of times each individual tower stops (say there are 20 towers). The problem I keep running into however is that each stop is not of uniform length in in duration so one stop can be 30 minutes long whereas another can be 60 minutes. My time stamps are in the following format and increase in 10 minute intervals:

TimeStamp
1/2/2009 12:20:00 AM

is there a way to tell my count that regardless if there is one or one thousand timestamps that correspond to one individual stop for an individual tower to count it as one?

Thanks

[June7]

Show more sample data.

A record is created every ten minutes regardless of status of the tower (stopped or not)? How do you know the tower is in stop state - the RPM field will read 0?

[jbr87]

Yes there is a record created regardless of the status of the tower stopped or not. the tower will be in a stopped state on two conditions if the rpm is less than 0.5 and if the winds peed is above 3.

I am able to successfully set up my stopped conditions I'm just looking to get my count going.

[June7]

Could you provide data? A spreadsheet would be ok.

[jbr87]

Here this is a small sample I modified to include some stops I hope this helps

what the search should turn up is

1 stop for

2301761

1 stop for

2301757 and 2 stops for 2301759

[June7]

The following returns 3 records with count:
SELECT TowerID, Count(TowerID) As CountStops FROM (SELECT Query1.TowerID
FROM (SELECT Table1.TowerID, (SELECT Top 1 Dupe.TimeStamp FROM Table1 AS Dupe WHERE Dupe.TowerID = Table1.TowerID AND Dupe.RPM>0 AND Dupe.Windspeed >3 AND Dupe.TimeStamp>Table1.TimeStamp ORDER BY Dupe.TowerID, Dupe.TimeStamp) AS NextValue
FROM Table1
WHERE [RPM]<0.5 And [Windspeed]>3
ORDER BY Table1.TowerID) AS Query1
GROUP BY Query1.TowerID, Query1.NextValue) GROUP BY TowerID;

This helped me construct nested query: http://allenbrowne.com/subquery-01.html

[jbr87]

It seems to be working but my dataset is pretty big so it's taken the better part of ten minutes to complete 1/8th of the querry I will report back when it has completed

[June7]

That small dataset you provided is instantaneous but I am not surprised a large dataset would take a while. You should test on the small set first.

Make sure field names are correct. Notice I did not use spaces in any names.

A VBA solution might run much faster.

[jbr87]

Agreed too bad I'm awful @ VBA it will just take me time I guess