Anonymous

<?php
$query_dates = $db->sql("SELECT ref_date, csr_id FROM rpt_broker_buyer_average ORDER BY ref_date DESC");
foreach ($query_dates as $query_dates_row) {
	
	$insert_date = $query_dates_row["REF_DATE"];
	$insert_csr = $query_dates_row["CSR_ID"];

	// As near as I can tell this would do the same thing. 2 queries might be faster.
	
	$db->sql_one("CREATE TEMPORARY TABLE tmp_table SELECT count(*) as cnt, ref_date FROM rpt_broker_buyer_average WHERE csr_id = '$insert_csr' AND  a.ref_date > to_date('$insert_date', 'DD-MM-YYYY') - 7 AND ref_date < to_date('$insert_date', 'DD-MM-YYYY') GROUP BY ref_date ORDER BY ref_date DESC");

	$insert_short = $db->sql_one("SELECT SUM(cnt) / 5 FROM tmp_table");
	/*
	// Your Query.
	$insert_short = $db->sql_one("SELECT SUM(cnt) / 5 FROM (SELECT count(*) as cnt, ref_date FROM rpt_broker_buyer_average WHERE csr_id = '$insert_csr' GROUP BY ref_date ORDER BY ref_date DESC) a WHERE a.ref_date > to_date('$insert_date', 'DD-MM-YYYY') - 7 AND ref_date < to_date('$insert_date', 'DD-MM-YYYY')");
		
	*/
	if(empty($insert_short) || !is_numeric($insert_short)) {$insert_short = '0';}
	
	$insert_long = $db->sql_one("SELECT SUM(cnt) / 10 FROM (SELECT count(*) as cnt, ref_date FROM rpt_broker_buyer_average WHERE csr_id = '$insert_csr' GROUP BY ref_date ORDER BY ref_date DESC) a WHERE a.ref_date > to_date('$insert_date', 'DD-MM-YYYY') - 14 AND ref_date < to_date('$insert_date', 'DD-MM-YYYY')");
	if(empty($insert_long) || !is_numeric($insert_long)) {$insert_long = '0';}	
	
	$db->sql("UPDATE rpt_broker_buyer_average SET ref_short = '$insert_short', ref_long = '$insert_long' WHERE ref_date = to_date('$insert_date', 'DD-MM-YYYY') AND csr_id = '$insert_csr'");
}
?>