SELECT DISTINCT SENSOR VALID READS FROM MULTIPLE DATA INPUTs

• guizonatto

  SSC Journeyman

  Points: 89

  More actions

  July 1, 2010 at 7:37 am

  #224359

  Dear all,

  I have a table with a lot of reads from sensors in field. This table is populated every hour with the sensors reads. So I have a lot of inputs from the same sensors.

  I am willing to select which sensors have at least 1 valid input today and how many sensors have send me reads (valids or invalids). Other factor, I have different locations, which can repeat the sensor logical number. My table is organized as follow:

  Sensor_Location Sensor_Logical_Number Status Aquisition_Date

  Sensor_Location = Sensor Location (FK to other table)

  Sensor_Logical_Number = sensor logical number

  Status = reading status

  Aquisition_Date = Date that the sensor was read

  Since I need some performance in the querry (I am using Index) and select the data in only 1 querry. I was not able to do that using the query below.

  SELECT SUM(VALID) AS VALID_READS, COUNT (*) - SUM(VALIDOS) AS INVALID_READS

  FROM (

  SELECT DISTINCT SENSOR_LOCATION,SENSOR_ID, CASE WHEN (STATUS & 128 <> 128) THEN 1 ELSE 0 END AS VALID

  FROM SENSOR_READS (NOLOCK)

  WHERE AQUISITION_DATE = GETDATE()

  ) A

  The problem with the code above is that when I have 1 read that is valid and 1 read that is invalid, I am counting twice to the total amount of sensors (if there is at least 1 valid read, I need to count only once and discard the other invalid_reads).

  So, I solved with the following statement, but since this table have a lot of inputs, I am using select twice to separete the amount of sensors I have read (valid or invalid).

  DECLARE @QUANT_SENSORS INT

  DECLARE @QUANT_VALID_READS INT

  SELECT@QUANT_SENSORS =COUNT(*) FROM (

  selectDISTINCT SENSOR_LOCATION, SENSOR_NUMBER

  FROMSENSOR_READS (NOLOCK)

  WHEREAQUISITION_DATE < GETDATE()

  ) A

  SELECT @QUANT_VALID_READS=COUNT(*)--, CASE WHEN (STATUS_CS & 128 <> 128) THEN 1 ELSE 0 END AS VALIDOS

  FROM

  (

  selectDISTINCT SENSOR_LOCATION, SENSOR_NUMBER

  FROMSENSOR_READS (NOLOCK)

  WHERE

  STATUS_CS & 128 <> 128

  AND

  AQUISITION_DATE < GETDATE()

  ) B

  PRINT@NUM_MED

  PRINT@NUM_READ_VALID

  I willing to do the same that the above queries does in only one query. Is it possible?

  I hope someone can help me. 🙂