#Thoughts on removing excessive entries from price table
# this one returns all entries from prices table nearest previous selected date, only if exists
#
# before running make a backup of the full_prices within database 
#	mysql> create table full_prices like prices;
#	mysql> INSERT INTO full_prices SELECT * FROM prices;
# then run this file
# after running, move compacted table into prices;
#
#	mysql> DROP TABLE prices;
#	mysql> RENAME TABLE local_prices TO prices;
#
#   to backup original prices (or full_prices) table
#	dave@NoneSuch:~/SQL$ mysqldump -u <username> -p <databasename> full_prices > ~SQL/full_prices.sql
#   to restore full_prices
#	dave@NoneSuch:~/SQL$ mysql -u dave -p DGH < ~SQL/full_prices.sql

#Loop on date every month, append each month 



########### code snippets #########
DELIMITER go
DROP PROCEDURE IF EXISTS compact_prices;
CREATE PROCEDURE compact_prices()
BEGIN

CREATE TABLE local_prices LIKE prices;				#creates empty table


set @e := LAST_DAY(CURDATE());					#end with current date
set @s := DATE_SUB(@e, INTERVAL 8 YEAR);			#start 8 years ago

set @d := @s;							#initialize loop

year_loop:  WHILE (@d<=@e) DO
	call get_prices(@d);					# inserts full_prices into local_prices for @d
	set @d := LAST_DAY(DATE_ADD( @d, INTERVAL 1 MONTH));	#do monthly
END WHILE year_loop;


END;


/********************************************* get_prices function *****************************/
# function to insert one date"s worth into local_prices for nearest date <= @d
DROP PROCEDURE IF EXISTS get_prices;
CREATE PROCEDURE get_prices(ddate CHAR(10))
BEGIN

INSERT IGNORE INTO local_prices SELECT * FROM (SELECT *
		FROM full_prices p WHERE DATE(date) <= ddate ORDER BY DATE DESC) AS x	# <=?
		GROUP BY commodity_guid;
# using DATE(date) removes the time portion so a 2013-12-31 21:00 date is selected with <= 2013-12-31

END;

go
DELIMITER ;

