iQuality | revolutionary new program

Note: The data for my question is on SQLFiddle right here where you can query it.

I have data from a table and put into a temp table using the below logic but the BETWEEN start and end date time stamps are dynamically generated based on other logic in the stored proc, etc.

I have some SQL data in a table running MySQL and below is what it looks like when I export with MySQL WorkBench.

I saw the Count number of rows that are not within 10 seconds of each other post and tried applying it accordingly here but I couldn't easily figure it out so I'm posting as a question for some help.

This is the data I start this in a table

Column Headers

TimeInt, TimeStr, IsInItValue, Value, IQuality

Data

Here's the SQL Fiddle I created with everything I talk about below but this query will specifically be set to only query records from the same one day e.g. 2017-11-29 so this example should suffice.

I have two data tables

1. Batches To Run (Table1)

   • This is the value of the number of batches there are to run
     • For example 2 of 25 where 25 is the value gotten from this table
       • I do not want to see any records with less than a 1 values with this query
         • I also only want to see records with a 3 value for the IQuality field

2. Batch Count (Table2)

   • This is the value of the batch number that's run
     • For example 2 of 25 where 2 is the value gotten from this table
       • I do not want to see the zero 0 values with this query

When I have only one value for a specific date in the BTR table I always get the expected result (i.e. 1 of 25, 2 of 25 and so on always).

When there is more than one "batches to run" record set in a day (per the example data) and its value is reset to another valid value but with a different and newer time stamp for the same day,

How do I prevent the joining of the BC records that are older than the newer/current/newest BTR record time stamp wise to prevent invalid results SQL Fiddle example I provided starts this issue at the records with the 2017-11-29T13:59:59Z time stamp (TimeStr)?

I don't want the older records from the BC table matched to the next/newer/newest time stamp record from the BTR table and want it to move onto the next true record value based on time so when newer BTR record exists, do not look at anything less than that time until another new record.

Create Table and Insert Data

I have a stored proc in a DB server that's bringing back a value of 5064803 when that record does not exist and the value should be 5064800 as per the query that builds the value of the variable.

I'm not sure if this is an issue with the value being of the FLOAT data type and the value in the record of the table ending in a double-zero or what but I cannot figure it out easily.

The table data types match those from the sensors that are set but this particular value from this sensor never actually gets set to a data type and it's usually always either a 1-8 digit INT with no decimal but I'd like to keep the data types the same as the correlated sensor just in case.

I've broke down the proc and I'm able to recreate the problem easily so I will post the detail below for those that may be able to help me figure out the issue and any workaround, etc.

Create Table

So I have an SQL table in MySQL that has data in it based on what an end-user / operator enters in at a device before they start a batch to run to set the size of a container before they run it through the process.

This is an industrial machine that fills up containers with x amount of product based on what the operator sets and that works just fine at this level to control the amount dumped in each container.

There's a need to report on this data with averages, etc. for any given day now and this is fine too but at times the data in the table which indicates which container size is being run through for the day is not changed if they run the same size containers for multiple days; this is fine as well since I can just use something like SELECT Value FROM Table ORDER BY TimeStr DESC LIMIT 1 to get the most recent value from the table that indicates what the container size was set to.

I'm having trouble determining how to be handle when this table has a value change in the middle of a day and dynamically figuring out the average per container since before that value change and after that value. The tables that records the container weight from a sensor and the container size that the end-user / operator sets all have date time stamps too.

Below is the stored proc that I have setup so far just to do some quick testing to confirm it works as expected and afterwards I'll go through and fine tune, etc. but I put this together quickly to at least confirm I can get it to work as expected from the SP at the SQL level.