Page 1 of 1

MySQL: Group by month-year for rolling total of unpaid order

Posted: Wed Mar 17, 2010 7:05 am
by batfastad
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

Re: MySQL: Group by month-year for rolling total of unpaid order

Posted: Wed Mar 17, 2010 12:11 pm
by pickle
This sounds tough & challenging. Consider this an "off the top of my head" query:

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))
Not sure if the GROUP BY clause will work as you want.

Re: MySQL: Group by month-year for rolling total of unpaid order

Posted: Wed Mar 17, 2010 12:19 pm
by batfastad
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

Re: MySQL: Group by month-year for rolling total of unpaid order

Posted: Wed Mar 17, 2010 2:14 pm
by pickle

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))

Re: MySQL: Group by month-year for rolling total of unpaid order

Posted: Wed Mar 17, 2010 5:11 pm
by batfastad
I think that's close but it doesn't get the correct figures.

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')
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

Re: MySQL: Group by month-year for rolling total of unpaid order

Posted: Fri Mar 19, 2010 4:57 am
by VladSun
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 :)