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.