Mathematical SQL Query

I have a table logging a floating point value from a scale (a weight). I’d like to evaluate the absolute value of the integral of this curve dynamically, using a triggered expression (triggered by a change in Scale_Weight). I’m attempting to perform some simple algebra based on the trapezoidal approx. with a sampling rate (b-a=1) of one:

(b-a)((f(a)+f(b))/2 - f(a))

The values f(a) and f(b) represent the 2 most recent values logged in my SQL Server table. I’ve attempted the following with an evalution error (Does MS SQL not recognize TOP?):

SELECT TOP 2 SUM(Scale_Weight) OVER(ORDER BY t_stamp DESC)/2.0
FROM table

This query evaluates, but simply divides the most recent value by 2:

SELECT SUM(Scale_Weight) OVER(ORDER BY t_stamp DESC)/2.0
FROM table

As you can see, I haven’t even attempted the absolute value or the subtraction of the “2nd most recent” value because I didn’t know how to reference a specific row (cell?). I also tried using the LAG function to find f(a) to no avail (Again, is LAG not included in MS SQL function library?). As a noob, I feel the math is doable in a single query, I just can’t find the proper syntax. Thanks in advance.

Ignition 7.7.1
Windows 7

Quick update:

```SELECT ABS(SUM(Scale_Weight) OVER(PARTITION BY quality_code ORDER BY t_stamp DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)/2.0) FROM TFL09MAT5R1```

Does anyone have ideas on how to subtract the value preceding the current row Scale_Weight value? Something like:

```SELECT ABS(SUM(Scale_Weight) OVER(PARTITION BY quality_code ORDER BY t_stamp DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)/2.0[color=#FF0000] - 1 FOLLOWING[/color]) FROM TFL09MAT5R1```

I do something similar with getting difference in datetimes in seconds, perhaps you can repurpose this query to fit your needs. This is in MSSQL and creates a new table with the values and the previous value in two columns where you can then take the difference of those values. I’m only doing the last 20 values, but it you want to do it on the whole table then remove the top 20 stuff. Hopefully it’s helpful!

```SELECT AVG(DATEDIFF(SECOND, b.t_stamp, a.t_stamp)) FROM ( SELECT top 20 * , ROW_NUMBER() OVER ( ORDER BY t_stamp desc) rn FROM Table WHERE Machine = 'Left' ) a JOIN ( SELECT top 20 * , ROW_NUMBER() OVER ( ORDER BY t_stamp desc) rn FROM Table WHERE Machine = 'Left' ) b ON ( a.rn = b.rn - 1 )```

Thanks for your input Duffanator, but I’m pursuing a simpler query that should approximate what I need. Hope I didn’t waste too much of your time.

I think Doug’s (Duffanator) point is that it’s not going to be a simple query. Subqueries are necessary to grab an aggregate (SUM, AVG, etc.) and go back and grab the second to last value.

Here’s a table I created (SQL Server 2000) called Table1

| 3 | 345.6 |

| 4 | 432.1 |
----------------[/code]

To get the average of the last two values (that’s what i/2[/i]) really is:

```SELECT AVG(sample) ## SUM(sample)/2 works also ## FROM (SELECT TOP 2 * FROM Table1 ORDER BY ndx DESC) t```

To get the second to last value

```SELECT TOP 1 sample FROM (SELECT TOP 2 * FROM Table1 ORDER BY ndx DESC) t ORDER BY ndx```

So that’s two queries. Now you need a third one to put it all together subtracting the second one (and get the absolute value-- I didn’t forget! ).

```SELECT ABS ((SELECT AVG(sample) FROM (SELECT TOP 2 * FROM Table1 ORDER BY ndx DESC) t) - (SELECT TOP 1 sample AS Expr1 FROM (SELECT TOP 2 * FROM Table1 ORDER BY ndx DESC) t ORDER BY ndx)```

Sometimes the trick is knowing when to use SQL or when it’s better to write a script:

[code]query=“SELECT TOP 2 * FROM Table1 ORDER BY ndx DESC”
result=system.db.runQuery(query, “database”)
b=result[0][“sample”] #remember, they came in reverse order
a=result[1][“sample”]

print abs((a+b)/2-a)[/code]

Hope this helps!

Jordan, thanks for the advice. I ended up digging into the PLC program to find the triggers I needed to obtain the values via gateway scripting (no queries). It’s not pretty, but it works!

```if initialChange == 0: if system.tag.read(pathFeederRun).value == 1: system.tag.write(pathFeederStart, system.tag.read(pathScaleWeight).value) while system.tag.read(pathFeederRun).value == 1: if system.tag.read(pathRefillMode).value == 0: while system.tag.read(pathRefillMode).value == 0: if system.tag.read(pathScaleWeight).value > system.tag.read(pathFeederStart).value: system.tag.write(pathFeederStart, system.tag.read(pathScaleWeight).value) else: system.tag.write(pathFeederEnd, system.tag.read(pathScaleWeight).value) while system.tag.read(pathRefillMode).value == 1: if system.tag.read(pathScaleWeight).value < system.tag.read(pathFeederEnd).value: system.tag.write(pathFeederEnd, system.tag.read(pathScaleWeight).value) system.tag.write(pathCycleWeight, system.tag.read(pathFeederStart).value - system.tag.read(pathFeederEnd).value) system.tag.write(pathTotalWeight, system.tag.read(pathTotalWeight).value + system.tag.read(pathCycleWeight).value) else: if system.tag.read(pathFeederStart).value != 0: system.tag.write(pathTotalWeight, system.tag.read(pathTotalWeight).value + system.tag.read(pathFeederStart).value - system.tag.read(pathScaleWeight).value)```

So I guess it’s not such a good idea to have while loops in gateway scripts. My CPU usage shot up to ~100% and could not establish consistent comms with many devices. The System Console log had many “ClockDriftDetector - Clock drift, degraded performance, or pause-the-world detected…”
warnings. I’m assuming this was caused by the script posted above? The initial trigger begins a while loop that may last 5 minutes (I had my reservations). Looks like I’ll have to find another way unless I hear from you all.