-- -------------------------------------------------------------------------------- -- Routine DDL -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`demo`@`%` PROCEDURE `daily_revenue_report`() BEGIN DECLARE last_month VARCHAR(2) DEFAULT '12'; DECLARE last_year VARCHAR(4) DEFAULT '2010'; DECLARE last_month_beg DATE; DECLARE last_month_end DATE; DECLARE this_month_beg DATE; DECLARE this_month_end DATE; DECLARE days_total INT; DECLARE days_so_far INT; SET this_month_end = DATE_SUB(CURDATE(), INTERVAL 1 DAY); SET last_month = CASE WHEN MONTH(this_month_end) = '1' THEN '12' ELSE MONTH(this_month_end) - 1 END; SET last_year = CASE WHEN MONTH(this_month_end) = '1' THEN YEAR(this_month_end) - 1 ELSE YEAR(this_month_end) END; SET last_month_beg = CONCAT(last_year,'-',last_month,'-01'); SET last_month_end = LAST_DAY(last_month_beg); SET this_month_beg = CONCAT(YEAR(this_month_end),'-',MONTH(this_month_end),'-01'); SET days_total = DAY(LAST_DAY(CURDATE())); SET days_so_far = DAY(CURDATE()); SELECT network_name, target, 0 as daily, today, mtd, 0 as mtd_percent, 0 as estimate FROM ( -- last_month SELECT network_id, network_name, ROUND(SUM(estimated_gross_revenue)*0.7,-2) AS target FROM yahoo_revenue_report NATURAL JOIN networks WHERE log_date >= last_month_beg AND log_date <= last_month_end AND type = 'contextlinks' GROUP BY network_id ) last_month NATURAL JOIN ( -- this_month SELECT network_id, network_name, ROUND(SUM(estimated_gross_revenue)*0.7,0) AS mtd FROM yahoo_revenue_report NATURAL JOIN networks WHERE log_date >= this_month_beg AND log_date <= this_month_end AND type = 'contextlinks' GROUP BY network_id ) this_month NATURAL JOIN ( -- this_day SELECT network_id, network_name, ROUND(SUM(estimated_gross_revenue)*0.7,0) AS today FROM yahoo_revenue_report NATURAL JOIN networks WHERE log_date = this_month_end AND type = 'contextlinks' GROUP BY network_id ) this_day ORDER BY target DESC; END