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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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))
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply