Calculating existing measurements for Cumulative BTUs

[dbrewerton]

Ok, this is that new post I said I was going to make.

Synopsis of situation:

I have two tables that have measurement slices. One table contains the time slices, the other contains the measurements.

ewise_slice
       |
       > slice_id
       > device_id
       > slice_timestamp
 
ewise_slice_msmnt
       |
       > slice_msmnt_id
       > slice_id
       > msmnt_type_id
       > msmnt_value
 

My cumulative BTU table has the following structure:

ewise_cumulative_btus
       |
       > id
       > device_id
       > curbtus
       > cumul_btus
       > ewise_slice
 

Now, what I'm trying to do is to step through the existing measurements in ewise_slice/_msmnt tables and thereby calculate my existing readings. I have a SP that will work but what I need to know is how to loop through my existing readings. I will keep working on this but any advice is appreciated :)

[dbrewerton]

Probably should include what I have so far. My problem is I don't know how to make this loop to the end. Here's the code:

###################
delimiter ;
 
DROP PROCEDURE IF EXISTS `calcbtus`;
DELIMITER $$
CREATE PROCEDURE `calcbtus`()
 
BEGIN
 
# slice1 is active slice - slice2 is the previous slice - time1 and time2 relate to these respectively
DECLARE slice1 bigint;
DECLARE slice2 bigint;
DECLARE time1 DATETIME;
DECLARE time2 DATETIME;
DECLARE x_device_id BIGINT;
DECLARE slicetimediff BIGINT;
 
#variables for BTUHr calc
DECLARE flowrate BIGINT;
DECLARE brinefactor BIGINT;
 
#BTU/Hr calc 1
DECLARE btuhr1 bigint;
DECLARE t1_1 BIGINT;
DECLARE t2_1 BIGINT;
DECLARE c1_1 BIGINT;
 
#BTU/Hr calc 2
DECLARE btuhr2 BIGINT;
DECLARE t1_2 BIGINT;
DECLARE t2_2 BIGINT;
DECLARE c1_2 BIGINT;
 
#calculation of (BTUHr1+BTUHr2)* 2 * slicetimediff/60
DECLARE btucalc bigint;
 
#Insertion values for cumulative BTUs
DECLARE lastbtuid BIGINT;
DECLARE lastbturead BIGINT;
DECLARE lastcurbtus BIGINT;
DECLARE x_id bigint;
 
# Create loop for slices
main_loop: LOOP
 
        SET x_id = slice1;
 
# Idea, compare maxsliceid to x_id? so if x_id > maxsliceid leave loop...not sure...
 
        if (x_id IS NULL) then
            set x_id = (select slice_id from ewise_slice order by slice_id limit 1);
        end if
 
        select device_id from ewise_slice where slice_id = x_id;
    select slice_id from ewise_slice where slice_id = x_id order by slice_id LIMIT 1 into slice1;
    select slice_id from ewise_slice where slice_id = x_id order by slice_id LIMIT 1,1 into slice2;
 
    select slice_timestamp from ewise_slice where slice_id = slice1 INTO time1;
    select slice_timestamp from ewise_slice where slice_id = slice2 INTO time2;
 
    SELECT TIMESTAMPDIFF(MINUTE,time2,time1) into slicetimediff;
    if (slicetimediff IS NULL) then
        set slicetimediff = 1;
    end if;
    if (slicetimediff > 60) then
        set slicetimediff = 1;
    end if;
 
    select egw_con_flowrate from egw_constants where egw_con_device_id = x_device_id INTO flowrate;
    select brine from egw_constants where egw_con_device_id = x_device_id INTO brinefactor;
 
    select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '1' INTO t1_1;
    select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '2' INTO t2_1;
    select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '3' INTO c1_1;
 
    select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '1' INTO t1_2;
    select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '2' INTO t2_2;
    select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '3' INTO c1_2;
 
    SELECT ABS(t2_1 - t1_1) * flowrate * brinefactor * c1_1 INTO BTUHr1;
    SELECT ABS(t2_2 - t1_2) * flowrate * brinefactor * c1_2 INTO BTUHr2;
 
    if (BTUHr2 IS NULL) then
        set BTUHR2 = 0;
    end if;
 
    SELECT (ABS(BTUHr1)+ABS(BTUHr2))/2*(slicetimediff/60) INTO btucalc;
 
    select id from ewise_cumulative_btus where device_id = x_device_id AND id = slice2 INTO lastbtuid;
 
    select ABS(cumul_btus) from ewise_cumulative_btus where id = lastbtuid INTO lastbturead;
 
    if (lastbturead IS NULL) then
        set lastbturead = 0;
    end if;
 
    if ABS(TIMESTAMPDIFF(MINUTE,time2,time1)) > 61 then
        set btucalc = 0;
    end if;
 
    select (ABS(btucalc) + ABS(lastbturead)) INTO lastcurbtus;
 
    if slicetimediff < 61 then
    insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus,slice_id) values(x_device_id,btucalc,lastcurbtus,slice1);
    else
    insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus,slice_id) values(x_device_id,btucalc,'0',slice1);
    end if;
 
    # Uncomment the line below for command line debugging
    select slice1,time1,slice2,time2,slicetimediff,BTUHr1,BTUHr2,lastbtuid,lastbturead,x_device_id,btucalc,lastcurbtus;
    set slice1 = slice1++
 
END LOOP main_loop;
END
 
$$
DELIMITER ;
###################
 

[dbrewerton]

Well, it appears the problem I have is in creating a loop in my SP. This makes absolutely no sense to me as my other SP has a loop and runs just fine. Every time I load the SP, I get error 1064 and its pointing at my opening loop statement which is:

main_loop: LOOP

Can anyone help me here? I was told by a programmer friend to declare the character set latin1 but that didn't help either.

[dbrewerton]

Never mind, I figured it out on my own. My problem was I was not properly declaring the slice1 variable's value. Once I had that problem licked, the query ran like a champ.