Query Help

Ignition
1

Please help.

I need to query a MySQL database that contains the data shown in the attached table. The query will need to be between a start date and end date. I also need the query to find the minimum “UV Dose” during that time period. Next I need to find the “Effluent Flow” and “UV Transmittance” values that occured when the minimum “UV Dose” occurred. Finally, I need to sum the values in the “Bank1A On”, “Bank1B On”, and “Bank1C On” that occured at the same time as the minimum “UV Dose” in order to get the number of Banks that were on during the min UV Dose occurence. Apologies for the wordiness. Any ideas?

Table.jpg983×481 103 KB

2

As a tag binding

SELECT `UV Dose`, `Effluent Flow`,`UV Transmittance`, (`Bank1A On`+`Bank1B On`+Bank1C On`) as `Number_Of_Banks` FROM sometable  WHERE `UV Dose` = (SELECT min(`UV Dose`) FROM sometable WHERE `Time` > {Root Container.Start Date.date} and `Time` < {Root Container.End Date.date} LIMIT 1)

We need more info, such as Database type, what are th eindexes on the tables, where do you want the query to be (Binding vs Jython), etc

3

Thanks Kyle.

  1. It is a MySQL database.
  2. The table index is “group_table_ndx”.
  3. I would like the query to be a binding on numeric labels.

Question 1: Does the LIMIT 1 return the first occuring minimum value or some random minimum value in the dataset? I ask because as shown in the table, the same minimum “UV Dose” value can occur at more than one time in the dataset. I need to make sure that the query consistently returns the corresponding values (Effluent Flow, UV Transmittance, etc…) for the returned min(UV dose).

Question 2: Originally I was using Jython expression bindings pointed at the table component shown previously to find the required values. The problem that I was getting was when there were NULL values in the data table would show little square boxes. It looked like the Jython expressions were having trouble ignoring the NULL values and returning MIN, MAX, and AVG values, so I decided to try using SQL queries instead since it appears that queries ignore any NULL values in the dataset. Your thoughts on which method is better/easier?

Thanks,
Matt

4

The LIMIT 1 is not necessary in that sub-query since the query is already returning the min UV Dose. If you didn’t have an aggregate function (like min) the LIMIT would return records matching the ORDER BY clause.

It is better to use SQL queries for the MIN, MAX and AVG since you can control the NULL values. You can return all three in one dataset to get each value out of it.