I am currently trying to get a weekly and monthly average value from an SQL Database using the 'Table Lookup' Datareference. Unfortunately, nothing that I have tried so far has worked out.
What I have tried so far:
SELECT Avg(Value) FROM [dbo.Values] WHERE InstrumentTag = '%Element%' AND DATEDIFF(day, Date, getdate()) < 7
does not work due to the function datediff not being recognized
SELECT Avg(Value) FROM [dbo.Values] WHERE InstrumentTag = '%Element%' AND Date >= #%Time%# -7d
does not work due to syntax
Any suggestions on how I can solve this issue without creating extra PIPoints that contain the last weeks/months date?
Thanks in advance!