I’m in the endeavor to work in a project that needs to execute some calculations based on time differences. I know PI AF is not too friendly with this type of task. But I figure out how to proceed using some SQL magic through PI OLEDB and AF Table Lookup data reference. I would like some advice to validate if what I’m doing is an option, or could be achieved using AF native functions (AF Analysis)?
First the problem. I have an EF template with some Event Attributes. I need to get the Max value timestamp of an Event Attribute. Getting the Max value was really easy but getting the timestamp for this is getting complicated (at least for my limited knowledge). Here comes the SQL trick/hack/craziness. I used PI OLEDB to get the grab the timestamp into an Event Attribute type DateTime. Since I’m using an Event Template Attribute filtering the data was easy using the EventFrameTemplateAttributeID field. Sample of SQL script used:
SELECT ef.name, ef.starttime, ef.endtime, s.time, s.value,s.valuedbl
where starttime > '*-90d'
ORDER BY StartTime DESC
INNER JOIN [DevServer].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID
INNER JOIN [DevServer].[Data].[EventFrameSnapshot] s ON s.EventFrameAttributeID = efa.ID
OPTION (FORCE ORDER, EMBED ERRORS)
Since I need to calculate more timestamps (refer to image attached below) I would like to ask if this approach is valid and sustainable? Could be possible to achieve the same using PI Analysis and Elements Attributes? I know I have some limitation using Events Attributes since I can’t use them in PI Analysis but, is there is a way to circumvent this?
I have come to think that I maybe need to create a Custom Data Reference in order to execute the whole calculations in this custom .dll. But this is something that I would like to avoid is there are better options.
Any feedback will be very welcome.