PI OleDB Enterprise - substract a duration to a date.

Discussion created by HDelesalle on Dec 5, 2013
Latest reply on Dec 6, 2013 by michaelh

I would like to ave an OleDB query returning the 3 data for  an attribute


 - value of the attribute


 - value of the attribute 1 hour before


 - variation of the attribute on the pas hour


I have the following






SELECT a1.Time as raw_date,
       a1.ValueDbl as "Current Value",
       a2.ValueDbl as "Previous Value",
       (a1.ValueDbl - a2.ValueDbl) / a1.ValueDbl as "Variation"
FROM [TEPNGDW].Asset.ElementHierarchy eh
INNER JOIN [TEPNGDW].Asset.ElementAttribute ea ON (ea.ElementID = eh.ElementID and  ea.Name = 'CHP' ) 
INNER JOIN [TEPNGDW].Data.Archive a1 ON a1.ElementAttributeID = ea.ID 
INNER JOIN [TEPNGDW].Data.Archive a2 ON (a2.ElementAttributeID = ea.ID and a2.Time between (a1.time - '7m')  and  (a1.Time - '5m'))
WHERE Eh.name IN ('11A') and a1.Time > '*-2h' and a2.Time > '*-2h'




I get an error  "  Overload resolution failed for (DateTime, Time) argument(s)."





I can solve my issue on that sample case bu reverting the role of the dates to change  the 'minus' in 'plus' but I would like to have more flexibilityin my coding and be able to substract a duration to a date.


Does anyone know the proper syntax for that ?



Thanks in advance.