SQL Server Performance

Counting data transitions

Discussion in 'General Developer Questions' started by ceandra, Aug 4, 2008.

  1. ceandra New Member

    We have a large database with engineering data from a number of sensors. Some data is boolean, some is analog. The time base is generally 1 second, but can vary for several reasons, and may be as slow as 30 seconds between datapoints. There is a column Date_Time.
    I would like to develop a tool or tools to look at transitions in the data. We would like to count the number of transitions in the data table or in a timeframe. For example, the booleans should be fairly easy, we need to count the number of times in a day in which the data goes from 0 to 1.
    The analog data is more difficult. For example, we would want to know how many times a temperature changes from below 200C to above 700C. However, teh temperature ramp could take anywhere from 10 seconds to 10 minutes. If I simply look at cases in which the data is >700, and look back 10 minutes, then I will end up counting multiple (many) hits if the ramp was very steep, and may miss some slower ramps.
    How can I count each transition only once?
    Thanks
    Chuck
  2. preethi Member

    The question is not very clear (at least to me). is it possible to add some sample data along with the table structure?
  3. FrankKalis Moderator

    The second requirement sounds like a case for a trigger to me, if I understand you correctly.

Share This Page