MySQL: Group by month-year for rolling total of unpaid order
Posted: Wed Mar 17, 2010 7:05 am
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
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