# Calculating between max & min values in SQL

**URL:** <https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296>\
**Category:** Ignition\
**Created:** [September 20, 2013, 4:33pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296 "2013-09-20T16:33:24Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cbabb](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbabb/32/49029_2.png) [@cbabb](https://forum.inductiveautomation.com/u/cbabb)\
**Post date:** [September 20, 2013, 4:33pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/1 "2013-09-20T16:33:24Z")

</div>

Having some trouble getting my head around something I was asked to to. If anyone has any suggestions, they would be greatly appreciated…

We collect information for a tank level. When the tank level reaches a specific point, we pump it out. Then the cycle repeats. The frequency is fairly constant but not exactly clockwork.

i.e. the graph looks pretty much like this.

![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/85233f0c41c080fb08eb6597206c880ebeebb5a7.png)

What they would like to know is

1. The frequency of cycles from low to high within the time frame shown in the chart (they select the time frame on an easy chart)

2. The time it takes to go from low to high for each cycle.

These values will be put into a numeric display field for the # of cycles, and a table for the times between cycles.

Suggestions on the right SQL query would be greatly appreciated…

Thanks

---

<div class="post-metadata">

**Author:** ![Powerplan](https://avatars.discourse-cdn.com/v4/letter/p/aca169/32.png) [@Powerplan](https://forum.inductiveautomation.com/u/Powerplan)\
**Post date:** [September 21, 2013, 3:33pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/2 "2013-09-21T15:33:28Z")

</div>

cbabb

It’s tricky unless you declare or the pump starts and stops at “exact” above and below levels

however if you log the pump running status or similar either as an event/alarm, or via a transaction group you could use that for both,

For the frequency you could count the number of runs(start) between periods, and for the time from low to high would be time period between running.

Chris

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [September 24, 2013, 4:09pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/3 "2013-09-24T16:09:30Z")

</div>

This one is tricky, but I would write a script that loops through the dataset. It looks like your values are always increasing (until they reset) so you could just grab the current and next values in a for loop and if the next value is less than the current value, then you know you are at a peak. From that you can calculate the number of peaks and time since the last peak.

---

<div class="post-metadata">

**Author:** ![Brett\_Yeomans](https://avatars.discourse-cdn.com/v4/letter/b/35a633/32.png) [@Brett\_Yeomans](https://forum.inductiveautomation.com/u/Brett_Yeomans)\
**Post date:** [September 25, 2013, 8:01am UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/4 "2013-09-25T08:01:12Z")

</div>

cbabb,  
If it helps I did something similar (ish) when tracking PLC counts. Since the PLC resets to zero when it reaches 32000 the graph looked the same as yours.

The first method was to join the table to itself on table1.ndx = table2.ndx and determine when the value in table1 is less than table2 and count the ‘rollover’. You could get the timestamps etc and do calculate your other data. My example is below, the result is from the first column of the temp table.

[code]DECLARE @T TABLE  
(ndx INT IDENTITY ( 1, 1 ) NOT NULL,  
open\_front INT  
,open\_rear INT  
,wrap\_count INT  
,t\_stamp DATETIME);

INSERT INTO @T VALUES (0,0,0,‘2013-01-01 01:00’);  
INSERT INTO @T VALUES (1,10,1000,‘2013-01-01 02:00’);  
INSERT INTO @T VALUES (1,20,2000,‘2013-01-01 03:00’);  
INSERT INTO @T VALUES (2,100,30000,‘2013-01-01 23:59’);  
INSERT INTO @T VALUES (2,100,31000,‘2013-01-02 01:00’);  
INSERT INTO @T VALUES (4,100,150,‘2013-01-02 01:30’);  
INSERT INTO @T VALUES (500,100,4000,‘2013-01-02 03:00’);  
INSERT INTO @T VALUES (4000,100,31000,‘2013-01-02 05:00’);  
INSERT INTO @T VALUES (7000,100,8,‘2013-01-02 08:00’);  
INSERT INTO @T VALUES (30000,100,4000,‘2013-01-02 10:00’);  
INSERT INTO @T VALUES (8,100,6000,‘2013-01-02 21:00’);

–The SELECT below adds the three values obtained((32000 - initial) + last value + (rollovers-1_32000))  
SELECT ISNULL(32000.0-A.open\_front + B.open\_front+ISNULL(C.[Rollover\_Count],-32000),0) AS [Count], A.Date  
FROM --Below is the initial value  
(SELECT  
ROW\_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t\_stamp)) ORDER BY t\_stamp) AS ‘RowNumber’,  
open\_front,  
dateadd(DAY,0, datediff(day,0, t\_stamp)) AS [Date]  
FROM @t  
WHERE t\_stamp \> ‘2012-12-31 23:59:59’ and t\_stamp \< ‘2013-07-02 23:59:59’)A – put report dates in here  
LEFT OUTER JOIN --Below is the end value  
(SELECT  
ROW\_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t\_stamp)) ORDER BY t\_stamp DESC ) AS ‘RowNumber’,  
open\_front,  
dateadd(DAY,0, datediff(day,0, t\_stamp))AS [Date]  
FROM @t  
WHERE t\_stamp \> ‘2012-12-31 23:59:59’ and t\_stamp \< ‘2013-07-02 23:59:59’)B  
ON A.Date = B.Date  
LEFT OUTER JOIN --Below is the number of rollovers  
(SELECT (COUNT(_)-1)\*32000.0 as [Rollover\_Count]  
,dateadd(DAY,0, datediff(day,0, t1.t\_stamp)) AS [Date]  
FROM @t t1  
FULL OUTER JOIN @t t2  
ON t1.ndx = t2.ndx -1  
WHERE t2.open\_front \< t1.open\_front AND t2.open\_front IS NOT NULL  
AND t1.t\_stamp \> ‘2012-12-31 23:59:59’ and t1.t\_stamp \< ‘2013-07-02 23:59:59’ – Also need report dates here  
group by dateadd(DAY,0, datediff(day,0, t1.t\_stamp)))C  
ON B.Date = C.Date  
WHERE A.Rownumber = 1 AND B.Rownumber = 1  
ORDER BY A.Date[/code]

I found this method very slow to execute when using a lot of data.  
Personally I opted for transaction groups that run queries against the data and decide when a ‘rollover’ (for you a drain and could be logged in an additional column as ‘1’ along with timestamps etc) has occured.

In my transaction group as well as recording the current value I have this triggered expression:

`SELECT CASE WHEN
ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC) ,0)
<= {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count} 
THEN 
(SELECT {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count} - ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC),0))
ELSE
(SELECT 32000 - ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC),0)+ {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count} )
END`

So every one minute (transaction group rate) I decide whether the value is higher than the last and if so record the difference, if it is lower than the last do something different (32000-last value + current). This method made the querying of SQL data way… easier when reporting on the values.

Probably not exactly what your after ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:")

---

<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:** [September 25, 2013, 7:15pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/5 "2013-09-25T19:15:15Z")

</div>

Being that the value of the tank level is likely coming from an analog loop, I rather doubt that it’s a nice sawtooth waveform. What your really looking for is the time between when the pump turns off and the time the pump turns back on.

My idea is similar to Bobby’s but I’d add a column in the table to store the pump running status. Then in your loop you only need to look for the transition in the pump status. Be a lot easier to hit than minor fluctuations in the level values.

Also looking for the transitions will enable you to extract full cycles, and not have to worry about the beginning and ending data that would likely be incomplete cycle data.

---

<div class="post-metadata">

**Author:** ![cbabb](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbabb/32/49029_2.png) [@cbabb](https://forum.inductiveautomation.com/u/cbabb)\
**Post date:** [September 25, 2013, 7:24pm UTC](https://forum.inductiveautomation.com/t/calculating-between-max-min-values-in-sql/7296/6 "2013-09-25T19:24:43Z")

</div>

[quote=“JordanCClark”]My idea is similar to Bobby’s but I’d add a column in the table to store the pump running status. Then in your loop you only need to look for the transition in the pump status. Be a lot easier to hit than minor fluctuations in the level values.  
[/quote]

ahhh, that was the problem to begin with. The pump isn’t coming into the PLC and all I had was a level.

What I chose to do, was write some PLC logic to give me a digital bit when I hit hi level and once that was stored in the database, I turn off the bit. Quick and simple and now I can query the database just looking for the the bits that are on and that gives me the timestamp I need to give the boss the info he wanted.

Thanks everyone!