# Mathematical SQL Query

**URL:** <https://forum.inductiveautomation.com/t/mathematical-sql-query/9185>\
**Category:** Ignition\
**Created:** [October 16, 2014, 10:13pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185 "2014-10-16T22:13:35Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![zfellers](https://avatars.discourse-cdn.com/v4/letter/z/b19c9b/32.png) [@zfellers](https://forum.inductiveautomation.com/u/zfellers)\
**Post date:** [October 16, 2014, 10:13pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/1 "2014-10-16T22:13:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zfellers](https://avatars.discourse-cdn.com/v4/letter/z/b19c9b/32.png) [@zfellers](https://forum.inductiveautomation.com/u/zfellers)\
**Post date:** [October 17, 2014, 1:46pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/2 "2014-10-17T13:46:21Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [October 17, 2014, 8:17pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/3 "2014-10-17T20:17:55Z")

</div>

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 )`

---

<div class="post-metadata">

**Author:** ![zfellers](https://avatars.discourse-cdn.com/v4/letter/z/b19c9b/32.png) [@zfellers](https://forum.inductiveautomation.com/u/zfellers)\
**Post date:** [October 20, 2014, 8:24pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/4 "2014-10-20T20:24:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 21, 2014, 10:53am UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/5 "2014-10-21T10:53:32Z")

</div>

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

## [code]\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ | ndx | sample |

## | 1 | 123.4 |

## | 2 | 234.5 |

## | 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]

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/38178fe1b00ac456e00f99819f988f0c3398c800.png)  

Hope this helps!

---

<div class="post-metadata">

**Author:** ![zfellers](https://avatars.discourse-cdn.com/v4/letter/z/b19c9b/32.png) [@zfellers](https://forum.inductiveautomation.com/u/zfellers)\
**Post date:** [October 21, 2014, 4:49pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/6 "2014-10-21T16:49:34Z")

</div>

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)`

---

<div class="post-metadata">

**Author:** ![zfellers](https://avatars.discourse-cdn.com/v4/letter/z/b19c9b/32.png) [@zfellers](https://forum.inductiveautomation.com/u/zfellers)\
**Post date:** [October 22, 2014, 4:37pm UTC](https://forum.inductiveautomation.com/t/mathematical-sql-query/9185/7 "2014-10-22T16:37:04Z")

</div>

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.