AnsweredAssumed Answered

How to calculate the number of occurrances as the number of excursions beyond its operating range in ASP.NET

Question asked by suyogd on Feb 26, 2015
Latest reply on Apr 2, 2015 by pthivierge

Hi,

 

I want to calculate the number of occurances as the number of excursions beyond its operating range using SQL query in ASP.NET

 

For e.g If i have a following data for which Design value = 45,Minimum = 10,Maximum=60

 

1/19/2015 0:013Out Of RangeOut Of Range
1/19/2015 0:0215.875In Range
1/19/2015 0:044.9375Out Of RangeOut Of Range
1/19/2015 0:051.9375Out Of Range
1/19/2015 0:0615.25In Range
1/19/2015 0:0818In Range
1/19/2015 0:09121.1875Out Of RangeOut Of Range
1/19/2015 0:11124.125Out Of Range
1/19/2015 0:12127.125Out Of Range
1/19/2015 0:1330.0625In Range
1/19/2015 0:1533.0625In Range
1/19/2015 0:16136.0625Out Of RangeOut Of Range
1/19/2015 0:18139.125Out Of Range
1/19/2015 0:1942.5In Range
1/19/2015 0:2145.5In Range
1/19/2015 0:2248.4375In Range
1/19/2015 0:231.5Out Of RangeOut Of Range
1/19/2015 0:254.375Out Of Range
1/19/2015 0:2657.5625In Range
1/19/2015 0:2760.3125Out Of RangeOut Of Range
1/19/2015 0:2963.25Out Of Range
1/19/2015 0:3066.375Out Of Range

Currently i get the count as 13,considering all deviated values.

 

I want to consider all consecutive values count as 1 which are beyond the range.

So in the above case ideally i should get the occurance count = 6 as shown in Last column.

 

Can anybody please tell me how to write the SQL query for the same ?

Outcomes