CODEWITHSUNDEEP
mysql
Bitcoin Murderous Maniac
Bitcoin Murderous Maniac

Reputation: 1478

Dynamically Control SQL Select Based on Time Stamps from other Table

What I Have

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.

New Reporting Need

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.

The Issue

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.

SQL Stored Proc

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.

delimiter $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
SET @CurrDate = CONCAT(CURDATE(),'%');

SET @CheckIt = (SELECT COUNT(*) FROM SL.ContainerOzBit
    WHERE TimeStr LIKE @CurrDate ORDER BY TimeStr DESC);

IF(@CheckIt = 0) THEN 
    SET @GramAvg = (SELECT AVG(Value) FROM SL.RTSensorWeight
    WHERE iQuality = 3 AND Value != 0 AND TimeStr LIKE @CurrDate);
    SET @CanSize = (SELECT CASE Value 
        WHEN 0 THEN '15' 
        WHEN 1 THEN '21'
        WHEN 2 THEN '12'
        WHEN 3 THEN '17'
        WHEN 4 THEN '17' 
    ELSE NULL END as CanSize
    FROM SL.ContainerOzBit
    WHERE IQuality = 3 ORDER BY TimeStr DESC LIMIT 1);

SET @CanSizeSetTime = (SELECT TimeStr FROM SL.ContainerOzBit WHERE IQuality = 3 ORDER BY TimeStr DESC LIMIT 1);
SET @Downtime = (SELECT Downtime FROM SL._trackingdowntime WHERE Date LIKE @CurrDate);
SELECT ROUND(@GramAvg * 0.03527396195, 2) AS OzAvg, ROUND(@GramAvg, 2) AS GramAvg, 
    @CanSize AS CanSize_Set, @CanSizeSetTime AS CanSize_SetTime, @Downtime AS Downtime;

ELSE 

    <I need to put query here to help get average but split up between the time of the change>

END IF;

END$$

Recap Clarification

So, if they run 21 Oz containers all day long and I send the report at the end of the day then this will be fine as we know the average all day long is accurate because the same size containers have been running all day long.

If they run 21 Oz containers for the first three hours of the day and then sometime after that I see there's a value change in the table indicating they started running 15 Oz cans at 11:30 AM for example, this is where I'm stuck where to go next if this is even possible with MySQL to figure out or write a query that can tell from 8 AM - 11:30 AM they were running 21 Oz containers and then from 11:30 AM - 5:00 PM they were running 15 Oz containers and then build the logic to show the averages, etc. from there for both those.

In the Stored Proc I provided, after the ELSE is where I'm stuck on how to proceed.

To make matters worse for some reason when the operator does change the value at the machine that updates the table, it will update the table with all the values until the last one that's set is the one it should be to indicate the container weight for the batch run (example below). If the value was changed and 10 seconds hasn't passed since it was last changed, then I really don't even need to look at that.

enter image description here

I wouldn't be surprised if I'm overlooking this or making it more complicated than it needs to be so any suggestions or guidance will be given a shot. I just need to be able to show them their averages for the day

Upvotes: 1

Views: 50

Answers (1)

Jan
Jan

Reputation: 13858

This should do most of your work, iving you the both the value and the can size at the time:

SELECT r.TimeStr, r.Value, (SELECT CASE Value 
    WHEN 0 THEN '15' 
    WHEN 1 THEN '21'
    WHEN 2 THEN '12'
    WHEN 3 THEN '17'
    WHEN 4 THEN '17' 
ELSE NULL END  FROM ContainerOzBit 
     WHERE TimeStr < r.TimeStr 
       ORDER BY TimeStr DESC LIMIT 1) as CanSize,
(SELECT TimeStr  FROM ContainerOzBit 
     WHERE TimeStr < r.TimeStr 
       ORDER BY TimeStr DESC LIMIT 1) as LastCanSizeChange
FROM RTSensorWeight r;

Resultig (on my demo data to):

enter image description here

Upvotes: 1

Related Questions