I have a query that is used to generate sales reports for specific timeframes (week to date, month to date, etc.), grouped by location (store 1, store 2, etc.). The query works great when getting a single time frame, but some reports require that it shows the selected time frame (for instance "week to date"), and the previous equivalent time frame ("last week to date"), for the report. I'm wondering if I can obtain all the needed info in one query.
Here is the query for a single time frame:
$sql = "SELECT l.*,
SUM(p.payment_amount) AS dollars_booked_t,
AVG(p.payment_amount) AS average_sale_t,
COUNT(o.order_id) AS number_booked_t,
SUM(p.payment_amount * .05) AS five_percent_t
FROM " . ORDERS_TABLE . " o, " . PAYMENTS_TABLE . " p, " . CAL_TABLE . " e, " . LOCATIONS_TABLE . " l
WHERE o.order_date >= '" . $stat_range['this_stats_start'] . "' AND o.order_date <= '" . $stat_range['this_stats_end'] . "' AND o.complete = '1'
AND p.order_id = o.order_id
AND e.event_id = o.event_id
AND l.location_id = e.location_id
GROUP BY l.location_id";
I have the necessary date ranges for the current and previous time frames generated through a separate function. The previous time ranges are $stat_range['last_stats_start'] and $stat_range['last_stats_end'].
I tried creating two instances of each table used (ex: orders o and orders ol) and basically duplicating the other parts of the query using those other instances and the previous time frames, but that didn't work. (I can explain the table structures if need be, but I didn't want to clutter up the post unnecessarily.) Any advice would be most appreciated. TIA!
[ EDIT: I ended up breaking it apart into two queries. ]
