MySQL derived table where clause with value from parent qry
Posted: Fri Jan 28, 2011 10:51 am
Hi everyone
I've developed an intranet orders/billing system which has a report which at any point in time outputs the totals of all orders that are yet to be billed, a forward order book total. So the client knows how much money is in the pipeline that's yet to be billed.
However I now need to create a similar report which shows how the forward order book total changes over time.
Here's the single query that outputs my current forward order totals...
So this outputs the totals for 2 products (amd/idn) and 3 currency totals for each.
Note the lack of a GROUP BY clause so the query just outputs the totals.
The date/time fields are all stored as unix timestamps in the DB (don't ask!) hence the UNIX_TIMESTAMP() function in the where clause to get the current time stamp. That basically excludes any orders that havn't been booked yet, or that have already been invoiced.
The first part of the WHERE clause, booking_stamp>=1230768000 simply narrows the forward order book total to all orders placed after 1st Jan 2009, as there are orders before that time period that won't have invoices in the system therefore skewing our totals.
One way to get the results of this query over time would be to loop the query in PHP for every month from 2009-01 to the current month, and pass the timestamp for the end of that month to the query instead of UNIX_TIMESTAMP() in the WHERE clause. But that would mean looping through the query 24-36 times... a horrible way of doing things.
I was wondering, is there a way to convert the query above into some sort of derived table or sub-query?
The problem being... how does the sub-query know the timestamp (year-month) that the parent query is currently iterating through?
So the values in the WHERE clause (currently where UNIX_TIMESTAMP() is) have to somehow be passed in from the parent query.
Is something like that even possible, or am I stuck with looping through this query in PHP and running it for each year-month?
Does this make any sense at all?
Cheers, B
I've developed an intranet orders/billing system which has a report which at any point in time outputs the totals of all orders that are yet to be billed, a forward order book total. So the client knows how much money is in the pipeline that's yet to be billed.
However I now need to create a similar report which shows how the forward order book total changes over time.
Here's the single query that outputs my current forward order totals...
Code: Select all
SELECT
SUM(CASE WHEN `adbookings`.`currency` = 'USD' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_usd`,
SUM(CASE WHEN `adbookings`.`currency` = 'EUR' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_eur`,
SUM(CASE WHEN `adbookings`.`currency` = 'GBP' AND `publications`.`sales_category` LIKE '%AMD%' THEN `adinserts`.`rate` ELSE NULL END) AS `amd_gbp`,
SUM(CASE WHEN `adbookings`.`currency` = 'USD' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_usd`,
SUM(CASE WHEN `adbookings`.`currency` = 'EUR' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_eur`,
SUM(CASE WHEN `adbookings`.`currency` = 'GBP' AND `publications`.`sales_category` LIKE '%IDN%' THEN `adinserts`.`rate` ELSE NULL END) AS `idn_gbp`
FROM `adinserts`
LEFT JOIN `adbookings` USING(`booking_id`)
LEFT JOIN `invoices` ON `adinserts`.`invoice_id`=`invoices`.`invoice_id`
LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
WHERE `adbookings`.`booking_stamp`>=1230768000 AND `adbookings`.`booking_stamp`<UNIX_TIMESTAMP() AND `adinserts`.`rate`>0 AND `adinserts`.`cancel_stamp` IS NULL AND (`invoices`.`invoice_stamp` IS NULL OR `invoices`.`invoice_stamp`>=UNIX_TIMESTAMP()) AND `publications`.`sales_category` NOT LIKE '%DW%' AND `publications`.`sales_category` LIKE '%ISSUE%'Note the lack of a GROUP BY clause so the query just outputs the totals.
The date/time fields are all stored as unix timestamps in the DB (don't ask!) hence the UNIX_TIMESTAMP() function in the where clause to get the current time stamp. That basically excludes any orders that havn't been booked yet, or that have already been invoiced.
The first part of the WHERE clause, booking_stamp>=1230768000 simply narrows the forward order book total to all orders placed after 1st Jan 2009, as there are orders before that time period that won't have invoices in the system therefore skewing our totals.
One way to get the results of this query over time would be to loop the query in PHP for every month from 2009-01 to the current month, and pass the timestamp for the end of that month to the query instead of UNIX_TIMESTAMP() in the WHERE clause. But that would mean looping through the query 24-36 times... a horrible way of doing things.
I was wondering, is there a way to convert the query above into some sort of derived table or sub-query?
The problem being... how does the sub-query know the timestamp (year-month) that the parent query is currently iterating through?
So the values in the WHERE clause (currently where UNIX_TIMESTAMP() is) have to somehow be passed in from the parent query.
Is something like that even possible, or am I stuck with looping through this query in PHP and running it for each year-month?
Does this make any sense at all?
Cheers, B