Want to find spikes in data

[jcfrasco]

I have a database that collects sensor data for customers and ocassionally there is a spike from one of the sensors that needs corrected. How can I create a stored procedure that will run through the data and find a value that jumps abnormally high from the value in the record before it and the record after it? The values constantly move up and down but they should never jump extremly high compared to the one before it and after it. Please help as the next problem will be to create a procedure that will automatically correct the spikes when they appear.

I appreciate any help on this issue.

JCFrasco

 

[gmmastros]

You can probably accomplish this with a self join. Without more information from you, though, it will be difficult to give better advice.

I urge you to post some sample data. While doing so, indicate what the primary key for the field will be. This will allow us to give you better advice in a more timely fashion.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jcfrasco]

Thanks George for your reply. The table is structured like this:

ID Date DeviceID RawData Data Pulse_Data
-------------------------------------------------------
0001 3/31/06 10626 64 64 0
0002 3/31/06 10626 67 67 0
0003 3/31/06 10626 95 85 0
0004 3/31/06 10626 65 65 0
0005 3/31/06 10626 62 62 0

As you can see the data rises and falls because it is based on consumption and depending on the time of day the data being returned will vary, but as you can see the third record is way higer than the others and causes a spike on the graph when the customer views his consumption for a specific period. i need to be able to select all records where the data extrodinarily high and needs to be addresses (depends on if the sensor is bad or if there was just a glitch). I hope this gives you the information you need. Thanks again for the assistance.

JCfrasco

 

[gmmastros]

Here's an example of the self join method (which I suspect may not work for you).

[green]-- Setting up dummy data in a table
-- variable for demonstration purposes.[/green]
Declare @Test Table(Id Integer, Date DateTime, 
        DeviceId Integer, RawData Integer, 
        Data Integer, Pulse_Data Integer)

Insert Into @Test Values(0001,'3/31/06',10626,64,64,0) 
Insert Into @Test Values(0002,'3/31/06',10626,67,67,0)
Insert Into @Test Values(0003,'3/31/06',10626,95,85,0)
Insert Into @Test Values(0004,'3/31/06',10626,65,65,0)
Insert Into @Test Values(0005,'3/31/06',10626,62,62,0)

[green]-- The query[/green]
Select A.*
from   @Test A
       Inner Join @Test B
         On  A.Id = B.Id - 1 
         And A.DeviceId = B.DeviceId
Where  A.RawData > B.RawData [!]+ 10[/!]

You should be able to copy/paste this in to query analyzer to see how this works.

For 'spike detection', I used [!]+ 10[/!], which also may not work for you.

There are different method for spike detection. If one value is 10 greater than the previous value, you can declare it as a spike. This would work well for some situations but not others. The spike detection method can become very complicated. This is a very simple example.

* Note: This method assumes...
1. That the ID column is an integer
2. It is consecutive.
3. There are no gaps.

If the previous conditions DO NOT APPLY, then you can create a table variable with an identity column to produce the same results. If you are unsure how to do this, let me know and I will assist more.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom