Hi Jeff

OK, aliasing table is creating a copy of one table but calling it something different, so you compare a table to itself e.g.:

FROM revenue a, revenue b, revenue c COULD ALSO BE FROM revenue AS a, revenue AS b, revenue AS c

I am referencing revenue three times but have aliased it as a, b, and c to make sure that my predicate makes sense.

As for the loop, I can give you something off the top of my head in rough (very!) PHP , if you don't come right, I can sit down and do the code a little more detailed

For simplification purposes, I am going to assume that you can alias tables as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest you check if this is possible - if you can't there is a work around that just requires a bit more thought....

//Assume you have an array that has all your client ids in

$client_id_array.

//Create base values based on the first id...

$select_base = "YEAR(1.date) AS year, MONTH(1.date) AS month,

$for_base = "FROM revenue 1"

$predicate_base = "WHERE 1.customer_id = ".$client_id_array[0]

//Now loop through and append additional items to each string for each instance of a client

//Start at 1 not zero as we already have accounted for the first id above

for($j=1; $j < count($client_id_array); $j++) { $select_base = $select_base.', SUM('.($j+1).') AS cust'.($j+1).'_rev';

$for_base = $for_base.', revenue '.($j+1);

$predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) = YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND '.($j+1).'.customer_id = '.$client_id_array[$j].')';

}

//Once your loop is done, put the parts together

$query = $select_base.$for_base.$predicate_base;

If you can't use numbers, you can use single letters, but that requires a little more work incrementing ASCII numbers and then converting to letters - also makes things way more complicated if you have more than 26 clients :) but still doable.

HTH