Sequential Index's used as a Filter

Archived Topics
1

First off, an example of what I have:

I have a table that contains 50 rows
Columns are: ndx, motor_current, t_stamp

the ndx is numbered 1 thru 50
the motor_current values are anywhere from 0 to 4000

I want to get all motor_current values that are greater than 2500

SELECT * FROM  l13_motor_current WHERE motor_current > 2500

OK, now lets say I have just 10 rows of results

ndx, motor_current, t_stamp
1, 2550, 2009-08-24 12:00:00
2, 2550, 2009-08-24 12:00:02
3, 2550, 2009-08-24 12:00:04
4, 2550, 2009-08-24 12:00:06
5, 2550, 2009-08-24 12:00:08
6, 2550, 2009-08-24 12:00:10
14, 2550, 2009-08-24 12:00:24
20, 2550, 2009-08-24 12:00:34
38, 2550, 2009-08-24 12:01:04
44, 2550, 2009-08-24 12:01:14

Now I would like to filter even deeper.

I only want results that are sequencial at least 5 times. Meaning, I want the results from 1-6 but not 14, 20, 38,44.

I’m really wanting to look for motor_current over a specified value and for a certain length of time.

Any Ideas? or do I need to clarify more?

2

Is the motor_current threshold always the same (or known by the PLC) for a given motor? In other words, can we easily break it down to a “realtime” (motor_over_current) bit, or do we have to be able to go back later to check the data (how long was it over 2000? 2500? 3000?)…

I’m still thinking about the SQL question (finding sequences in a query result)…

3

We want ALL the data for trending, but we only want THIS specific data for reporting we are trying to do. This is JUST for information and nothing else. We could do an OVER CURRENT bit if we wanted to I guess, but it would be nice to do CUSTOM queries for OVER CURRENT based on user input. Meaning: I might want to see everything over 2550 amps and someone ELSE might want anything over 3000 amps.

As far as the SEQUENCE is concerned, my ultimate goal would be to CALCULATE the amount of time PER incident that the motor was OVER a specified threshold.

4

I’m thinking a stored procedure or scripting function is going to save you a serious headache here. Either one of those could quite easilly zip through the result keeping track of the size and time length of sequences that have monotonically increasing index values.