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. I think this will involve joining the tables to themselves or possibly a sub-select. I always thought it best to avoid sub-selects apart from when absolutely necessary.

Basically 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 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.

Anyone got any suggestions on what I should join to what and what I need to group by?

Cheers, B