StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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?</p> <p>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?</p> <p>Unbilled customers are those that have no invoice...</p> <p>If so, something like:</p> <pre><code> 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)) </code></pre> <p>...should get you there.</p> <p>If you're looking for the collection cost for things over 30 days or something, you want something like:</p> <pre><code>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)) </code></pre> <p>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:</p> <pre><code>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) </code></pre> <p>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.</p> <p>Ideally, what you want is a monthly report archived and reported upon.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload