Integrate an on-demand multi-user report builder into an Indusoft screen.
Technical Problems:
Whether you are saving data by minute or by tag changes holes will exist if the project is shut down for any number of reasons. The MSSQL "AVG" function alone is not enough for doing real averages.
This MSSQL query below should fix all problems but it is very slow when computing MSSQL float datatypes or “Indusoft Real datatype” I was wondering if anyone could take a look at this query and see where my problem is. Bool and Int Datatypes are extremely fast and usefull but float takes quite some time. Microsoft has stated using compare operators such as =,<,> is not a good idea when dealing with floats. Also floats hardly ever retain the value initially assigned to them. “never use them for accounting”.
NOTES:
The attached version is not multi-user yet, I need to use the $RAND() function to create user output files for each user session. Once this is done the report builder can be deployed on a server and anyone can create their own reports at the same time.
Query 1:
This query is the heart of the Dynamic Report Builder and is inside the attached project. This is in text form to directly copy and paste into SQL Management Studio. In the below "With statements" there is some redundancy, This is for testing each layer of the with statements.
-----------------------------------------------------------------------Begin of query, copy and paste this into your system
declare @s DATETIME2
declare @e DATETIME2
declare @i INT
set @s = '2017-01-01 00:00:00' -------Change to your time
set @e = '2017-01-01 23:59:59' -------Change to your time
set @i = 60 -- Set for 60 minute averages change this value to 2880 for two days!
;WITH ALL_INTERVALS
AS (
Select CAST(@s as datetime) As TIMES, Null AS VALUE
UNION ALL
Select DateAdd(Minute, 1, TIMES), Null AS VALUE
FROM ALL_INTERVALS
WHERE DateAdd(Minute, -1, @e) > TIMES
),
ALL_TIMES
AS (
SELECT
H.Time_Stamp as TIMES,
H.YourTagGoesHere AS VALUE ------------------------------------------------------------------------- Change this to your tag
FROM YourDataBase.dbo.YourTable H ------------------------------------------------------ Change this to your database
WHERE Time_Stamp BETWEEN @s and @e
UNION ALL
SELECT
AI.TIMES AS TIMES,
AI.VALUE AS VALUE
FROM ALL_INTERVALS AI
),
FILL_UP_DOWN_ACCROSS
AS (
SELECT
TIMES AS TIMES,
ISNULL(AT.VALUE, (SELECT TOP 1 VALUE FROM ALL_TIMES WHERE TIMES = AT.TIMES AND VALUE IS NOT NULL ORDER BY TIMES ASC)) AS VALUE2,
ISNULL(AT.VALUE, (SELECT TOP 1 VALUE FROM ALL_TIMES WHERE TIMES > AT.TIMES AND VALUE IS NOT NULL ORDER BY TIMES ASC)) AS VALUE3,
ISNULL(AT.VALUE, (SELECT TOP 1 VALUE FROM ALL_TIMES WHERE TIMES < AT.TIMES AND VALUE IS NOT NULL ORDER BY TIMES DESC)) AS VALUE4 FROM ALL_TIMES AT),
COMBINE
AS (
SELECT TIMES,
COALESCE( CASE WHEN FUDA.VALUE2 IS NULL AND FUDA.VALUE4 IS NULL THEN (FUDA.VALUE3) ELSE FUDA.VALUE2 END,
CASE WHEN FUDA.VALUE2 IS NULL AND FUDA.VALUE3 IS NULL THEN (FUDA.VALUE4) ELSE FUDA.VALUE2 END,
CASE WHEN FUDA.VALUE2 IS NULL AND FUDA.VALUE4 IS NOT NULL THEN (FUDA.VALUE4) ELSE FUDA.VALUE2 END
) AS VALUE5
FROM FILL_UP_DOWN_ACCROSS FUDA
)
--We need to select the ending part of our query to do the fallowing, avg, min, max, sum.
SELECT DATEADD(MINUTE,((DATEDIFF(MINUTE,@s,TIMES)/@i)*@i),@s) AS TIMES,
(SUM( CONVERT(FLOAT,CAST(TIMES AS DATETIME)) * VALUE5) / SUM(CONVERT(FLOAT,CAST(TIMES AS DATETIME)))) AS TagAvg
FROM COMBINE
GROUP BY DATEADD(MINUTE,((DATEDIFF(MINUTE,@s,TIMES)/@i)*@i),@s)
ORDER BY DATEADD(MINUTE,((DATEDIFF(MINUTE,@s,TIMES)/@i)*@i),@s)
Option(MAXRECURSION 0)
-------------------------------------------------------------------------------------End of query
____________________________________________________________________________________________________________________
HOW TO USE THE DYNAMIC REPORT BUILDER
____________________________________________________________________________________________________________________
Step 1: Click on the database link connection icon and connect to your "Test" Indusoft database with existing data.
Step 2: Click on Clear Report to refresh the database connection tags.
Step 2: Click on refresh taglist. If your connection is good the first grid object will populate will all of your trend tags.
Step 3: Select your Start Date, End Date, and Interval.
Step 4: Add tags! Click on a tags in the first grid object and then click on the green add tag button.
Step 5: Run Report.
Step 6: If you selected a tag with a real data type you will have to wait a while, 10min to 30min. Integers and Boolean's are almost instant.
Step 7: Look over the first SQL query and make floating points just as fast as integers and Booleans, then and post your answer. I can do it in Oracle 11gR2 But not MSSQL..........
if you are interested in something like the above query for Oracle, copy and paste the link below. I build this query a while back for a completely different scada system. In oracle i am able to grab the previous and last value outside of my interval range. This is nice if gaps exist in the begging and end of your desired start and end times.
http://stackoverflow.com/questions/30724714/in-oracle-11g-how-do-you-time-weight-average-data-hourly-between-two-dates
_____________________________________________________________________________________________________________________