MySQL get point in time totals from related tables

Question

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

Answer 1

SELECT  yr, mon, COALESCE(SUM(price), 0)
FROM    (
        SELECT  2010 AS yr
        UNION ALL
        SELECT  2009
        UNION ALL
        …
        ) as years
CROSS JOIN
        (
        SELECT  1 AS mon
        UNION ALL
        SELECT  2
        …
        UNION ALL
        SELECT  12
        ) as months
LEFT JOIN
        orders o
ON      o.order_stamp <= UNIX_TIMESTAMP(CAST(CONCAT(year, mon, '01') AS DATE))
LEFT JOIN
        order_lines ol
ON      ol.order_id = o.order_id
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    invoice i
        WHERE   i.invoice_id = ol.invoice_id
                AND i.invoice_stamp > UNIX_TIMESTAMP(CAST(CONCAT(year, mon, '01') AS DATE))
        )
GROUP BY
        yr, mon

Answer 2

The dates in your schema are messing me up a bit...the order stamp is the UTC that the order was placed, right? And invoice_stamp is the UTC when the customer was invoiced?

I'm not sure what you mean by "rolling monthly totals", so I'm going to assume that it's a collection of all invoices for a given month? And a collection of all orders for a given month?

Unbilled customers are those that have no invoice...

If so, something like:

   SELECT YEAR(FROM_UNIXTIME(order_stamp)), 
          MONTH(FROM_UNIXTIME(order_stamp)), 
          SUM(price)
   FROM orders
      LEFT JOIN order_lines USING (order_id)
      LEFT JOIN invoices USING (invoice_id)
   WHERE invoices.invoice_id IS NULL
   GROUP BY YEAR(FROM_UNIXTIME(order_stamp)), 
            MONTH(FROM_UNIXTIME(order_stamp))

...should get you there.

If you're looking for the collection cost for things over 30 days or something, you want something like:

SELECT YEAR(FROM_UNIXTIME(invoice_date)), 
       MONTH(FROM_UNIXTIME(invoice_date)), 
       SUM(OverThirtyDayPrice),
       AVG(CollectionDays)
FROM 
    (
     SELECT invoice_date,
            IF(
               DATEDIFF(
                        FROM_UNIXTIME(IFNULL(invoice_stamp, UNIX_TIMESTAMP()), 
                        order_stamp) > 30, 
               price, 
               0
              ) AS OverThirtyDayPrice,
            DATEDIFF(FROM_UNIXTIME(IFNULL(invoice_stamp, UNIX_TIMESTAMP()),
                     order_stamp)) AS CollectionDays
     FROM orders
         LEFT JOIN order_lines USING (order_id)
         LEFT JOIN invoices USING (invoice_id)
    )
GROUP BY YEAR(FROM_UNIXTIME(invoice_date)), 
         MONTH(FROM_UNIXTIME(invoice_date))

We can hack up this last one a bit to make it work for things that were ordered in one month, but billed in another:

SELECT YEAR(invoice_datetime), 
       MONTH(invoice_datetime), 
       SUM(OutOfMonthPrice)
FROM 
    (
     SELECT FROM_UNIXTIME(IFNULL(invoice_stamp, UNIX_TIMESTAMP())) AS invoice_datetime,
            IF(MONTH(
                     FROM_UNIXTIME(IFNULL(invoice_stamp, UNIX_TIMESTAMP()))
                    ) 
               <> 
               MONTH(FROM_UNIXTIME(order_stamp)),
               price, 
               0
              ) AS OutOfMonthPrice,
     FROM orders
         LEFT JOIN order_lines USING (order_id)
         LEFT JOIN invoices USING (invoice_id)
    )
GROUP BY MONTH(invoice_datetime), YEAR(invoice_datetime)

If you want to get "point in time" queries, you may find that an RDBMS isn't the thing you need (you kinda need to iterate over a series of queries, with each one being for a specific month/year) and/or that your fundamental design doesn't support the report you're trying to generate.

Ideally, what you want is a monthly report archived and reported upon.