Hi everyone
We have an order book and invoicing system and I've been tasked with trying to output monthly rolling totals from these tables.
But I don't know really where to start with this. I think there's some SQL syntax that I don't even know about yet. I'm familiar with INNER/LEFT/JOINS and GROUP BY etc but grouping by date is confusing since I don't know how to limit the data to only the current date that's being grouped by at that point.
The system has 3 tables
orders: order_id, currency, order_stamp
orders_lines: order_line_id, invoice_id, order_id, price
invoices: invoice_id, invoice_stamp
order_stamp and invoice_stamp are UTC unix timestamps stored as integers, not MySQL's timestamps.
I'm trying to get a listing by year/month showing the total of current unbilled orders (sum of price), at that point in time.
Current orders are ones where order_stamp is less than or equal to 00:00 on the 1st of the month.
Unbilled orders are ones where invoice_stamp is null or invoice_stamp is greater than 00:00 on the 1st of the month.
At that point in time there may not be a related invoice yet and invoice_id might be null.
I can work out totals using SUM( CASE WHEN...) and I'd have to get funky by joining the table to itself somehow.
But when doing a rolling total like this, how would the JOIN know what month is currently being interated?
... If that makes sense
So if I did a sub-select or join on itself, how does it know what month to restrict the aggregate function to?
Anyone got any suggestions on what I should join to what and what I need to group by?
Cheers, B
MySQL: Group by month-year for rolling total of unpaid order
Moderator: General Moderators
Re: MySQL: Group by month-year for rolling total of unpaid order
This sounds tough & challenging. Consider this an "off the top of my head" query:
Not sure if the GROUP BY clause will work as you want.
Code: Select all
SELECT
SUM(price)
FROM
orders AS o,
orders_lines AS ol
WHERE
o.order_id = ol.order_id AND
(ol.invoice_stamp IS NULL OR
(DAY_OF_MONTH(FROM_UNIXTIME(ol.invoice_stamp)) >= 1 AND
TIME(FROM_UNIXTIME(ol.invoice_stamp)) > 0
)
)
GROUP BY
MONTH(FROM_UNIXTIME(ol.order_stamp))Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: MySQL: Group by month-year for rolling total of unpaid order
Ok interesting... tough and challenging sounds horrible though 
One quick thing... the invoice_stamp column is in the invoices table, not orders_lines.
So there needs to be a join between orders_lines.invoice_id and invoices.invoice_id but I'm not sure if just adding a regular LEFT JOIN after the FROM clause would break what you're suggesting.
Thanks for the info so far
Cheers, B
One quick thing... the invoice_stamp column is in the invoices table, not orders_lines.
So there needs to be a join between orders_lines.invoice_id and invoices.invoice_id but I'm not sure if just adding a regular LEFT JOIN after the FROM clause would break what you're suggesting.
Thanks for the info so far
Cheers, B
Re: MySQL: Group by month-year for rolling total of unpaid order
Code: Select all
SELECT
SUM(price)
FROM
orders AS o,
orders_lines AS ol,
invoices AS i
WHERE
o.order_id = ol.order_id AND
i.invoice_id = ol.invoice_id AND
(i.invoice_stamp IS NULL OR
(DAY_OF_MONTH(FROM_UNIXTIME(i.invoice_stamp)) >= 1 AND
TIME(FROM_UNIXTIME(i.invoice_stamp)) > 0
)
)
GROUP BY
MONTH(FROM_UNIXTIME(ol.order_stamp))Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: MySQL: Group by month-year for rolling total of unpaid order
I think that's close but it doesn't get the correct figures.
But how about this as an alternate?
Where yyyy-mm is somehow meant to be the year/month of the parent query. I think the sub-select within the FROM clause should be correct. But now I'm totally confused so I'll keep thinking on it until I get any more inspiration.
EDIT: Oh yeah I've just thrown a currency breakdown in there as well but shouldn't affect things
Cheers, B
But how about this as an alternate?
Code: Select all
SELECT FROM_UNIXTIME(`orders`.`order_stamp` , '%Y%m') AS `MONTH`, SUM(CASE WHEN `orders`.`currency` = 'USD' THEN `orders_lines`.`price` ELSE NULL END) AS `usd`, SUM(CASE WHEN `orders`.`currency` = 'EUR' THEN `orders_lines`.`price` ELSE NULL END) AS `eur`, SUM(CASE WHEN `orders`.`currency` = 'GBP' THEN `orders_lines`.`price` ELSE NULL END) AS `gbp` FROM (SELECT `orders`.`currency`, `orders_lines`.`price` FROM `orders_lines` LEFT JOIN `orders` USING(`order_id`) LEFT JOIN `invoices` ON `orders_lines`.`invoice_id`=`invoices`.`invoice_id` WHERE `orders`.`order_stamp` <= UNIX_TIMESTAMP('yyyy-mm-01 00:00:00') AND (`orders_lines`.`invoice_id` IS NULL OR `invoices`.`invoice_stamp` > UNIX_TIMESTAMP('yyyy-mm-01 00:00:00') ))GROUP BY FROM_UNIXTIME(`orders`.`order_stamp`, '%Y%c')EDIT: Oh yeah I've just thrown a currency breakdown in there as well but shouldn't affect things
Cheers, B
Re: MySQL: Group by month-year for rolling total of unpaid order
I'd suggest you to change your DB design 
I don't like using specific values of a column to indicate a record "status", which is unrelated to this column value.
So, create a `orders`.`status` column (ENUM, bitmask ...), and use NULL values for columns order_stamp, invoice_id when they are yet unknown.
Also, this `status` column may be a reference (so, it becomes `status_id`) to a `status` table (id, name), thus having an unknown number of "statuses" is not a problem. You can also store a status history, by having a third table (order_id, status_id, timestamp). Just another set of suggestions
I don't like using specific values of a column to indicate a record "status", which is unrelated to this column value.
So, create a `orders`.`status` column (ENUM, bitmask ...), and use NULL values for columns order_stamp, invoice_id when they are yet unknown.
Also, this `status` column may be a reference (so, it becomes `status_id`) to a `status` table (id, name), thus having an unknown number of "statuses" is not a problem. You can also store a status history, by having a third table (order_id, status_id, timestamp). Just another set of suggestions
There are 10 types of people in this world, those who understand binary and those who don't