Calculating between max & min values in SQL

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.

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

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

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.

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-132000))
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:

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.

[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!