struchej

Determine limit violations with OLE DB

Discussion created by struchej on Nov 7, 2013
Latest reply on Jan 7, 2014 by ssancov

I’m looking for a faster and more accurate way to determine limit violations with OLE DB.

We have this statement:

 

 

 
SELECT psatt1.time, CAST(psatt1.value AS float32) AS psattnumeric, CAST(value1.value AS float32) AS valuenumeric, CAST(value1.value AS float32) - CAST(psatt1.value AS float32) AS result,
case
when ((CAST(value1.value AS float32) - CAST(psatt1.value AS float32)) BETWEEN -100 AND 100)
then 'ok'
else 'alert'
end as comparison
FROM
(
SELECT time, value
FROM [piarchive].[picalc]
WHERE expr = 'StmSI_PsatT(''235.K01.33.001.1TI3208.VALUE'') * 10'
AND time BETWEEN '2013-09-17 08:24:53.714' AND '2013-09-18 10:14:42.767'
AND timestep = '1s'
) AS psatt1 INNER JOIN [piarchive].[piinterp2] AS value1 ON psatt1.time = value1.time
WHERE value1.tag = '235.K01.33.001.1PI3205.VALUE'
UNION
SELECT psatt2.time, CAST(psatt2.value AS float32) AS psattnumeric, CAST(value2.value AS float32) AS valuenumeric, CAST(value2.value AS float32) - CAST(psatt2.value AS float32) AS result,
case
when ((CAST(value2.value AS float32) - CAST(psatt2.value AS float32)) BETWEEN -100 AND 100)
then 'ok'
else 'alert'
end as comparison
FROM
(
SELECT time, value
FROM [piarchive].[picalc]
WHERE expr = 'StmSI_PsatT(''235.K01.33.002.2TI3208.VALUE'') * 10'
AND time BETWEEN '2013-09-17 08:24:53.714' AND '2013-09-18 10:14:42.767'
AND timestep = '1s'
) AS psatt2 INNER JOIN [piarchive].[piinterp2] AS value2 ON psatt2.time = value2.time
WHERE value2.tag = '235.K01.33.002.2PI3205.VALUE'

 

 

As you can see we compare calculated values against other values with a tolerance of +/-100.

 

All tags are interpolated.

We need start, end and extreme of limit violations.

Is there a built-in function or something else for doing this faster and more accurate?

Thank you!

Outcomes