Nested sort in SELECT followed by Conditional INSERT based upon results of SELECT inquiry

user_19007466 0
Viewed 28

I have been struggling with the following for some time. The server I am using has MySQL ver 5.7 installed.

The issue: I wish to take recorded tank level readings from one table, find the difference between the last two records for a particular tank, and multiply this by a factor to get a quantity used. The extracted quantity, if it is +ve, else 0 , then to be inserted into another table for further use.

The Quant value extracted may be +ve or -ve as tanks fill and empty. I only require the used quantity -ie falling level.

The two following tables are used:

Table 'tf_rdgs' sample; value 1 is content height.

id location value1 reading_time
1 18 1500
2 18 1340
3 9 1600
4 18 1200
5 9 1400
6 18 1765 yyyy
7 18 1642 xxxx

Table 'flow' example

id location Quant reading_time
1 18 5634 dd-mm: HH-mm
2 18 0 dd-mm: HH-mm
3 18 123 current time

I do not require to go back over history and am only interested in the latest level readings as a new level reading is inserted.

I can get the following to work with a table of only one location.

INSERT INTO flow (location, Quant) 
SELECT t1.location, (t2.value1 - t1.value1) AS Quant 
FROM tf_rdgs t1 cross join tf_rdgs t2 on t1.reading_time > t2.reading_time 
ORDER BY t2.reading_time DESC limit 1

It is not particularly efficient but works and gives the following return from the above table.

location Quant
18 123

for a table with mixed locations including a WHERE t1.location = ... statement does not work.

The problems i am struggling with are

  1. How to nest the initial sorting by location for the subsequent inquiry of difference between the last two tank level readings. A singular location search is ok rather than all tanks.

  2. A Conditional INSERT to insert the 'Quant' value only if it is +ve or else insert a 0 if it is -ve (ie filling)

I have tried many permutations on these without success.

  1. Once the above has been achieved it needs to run on a conditional trigger - based upon location of inserted data - in the tf_rdgs table activated upon each new reading inserted from the sensors on a particular tank.

I can achieve the above with the exception of the conditional insert if each tank had a dedicated table but unfortunately I cant go there due existing data structure and usage.

Any direction or assitance on parts or whole of this much appreciated.

mysql
0 Answers
Related