StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>keeping in mind that for each day, the row to use for the sum is the last one by date for >that day, for that salesperson)</p> </blockquote> <p>This information is hard to swallow. I was wondering wether you were saying that the sum for a day is stored in the salesperson_hourly_performance table, mixing day summaries and hourly summaries in the same table. </p> <p>There's no relation in your example to the division and company. But to break down sales per person per day for a given date range:</p> <pre><code>select s.name,substring(timestamp,1,11) as day,sum(callsInBound),sum(callsOutBound),sum(issuedOrders),sum(salesRevenue) from salesperson_hourly_performance facts , salesperson s where facts.salesPersonId = s.id and timestamp >= "2009-05-03 00:00:00" and timestamp < "2009-05-07 00:00:00" group by s.name,day order by day asc; +-----------+-------------+-------------------+--------------------+-------------------+-------------------+ | name | day | sum(callsInBound) | sum(callsOutBound) | sum(issuedOrders) | sum(salesRevenue) | +-----------+-------------+-------------------+--------------------+-------------------+-------------------+ | bob jones | 2009-05-03 | 101 | 125 | 93 | 72836.7372 | | bob jones | 2009-05-04 | 19 | 17 | 6 | 4200.7100 | | bob jones | 2009-05-06 | 0 | 2 | 1 | 120.0000 | +-----------+-------------+-------------------+--------------------+-------------------+-------------------+ </code></pre> <p>Storing the timestamp as an actual timestamp/datetime type would give you easier flexibility dealing with dates and times. There's mysql functions for converting strings to datetimes that probably could help your queries if it really has to be a varchar column</p> <p>Edit, I would really not mix granularity in this table. Keep one table for day summaries, one table for hours.</p> <p>if you'd only need the row with the largest date per day use e.g. </p> <pre><code>SELECT p.name, Substring(TIMESTAMP,1,11) AS DAY, Sum(callsinbound), Sum(callsoutbound), Sum(issuedorders), Sum(salesrevenue) FROM (SELECT sh.salespersonid, Substring(sh.TIMESTAMP,1,11) AS DAY, Max(TIMESTAMP) AS max_ts FROM salesperson_hourly_performance sh GROUP BY sh.salespersonid, DAY) t INNER JOIN salesperson_hourly_performance shp ON t.salespersonid = shp.salespersonid AND t.max_ts = shp.TIMESTAMP INNER JOIN salesperson p ON shp.salespersonid = p.id GROUP BY p.name, DAY; </code></pre> <p>Add where clauses where you need e.g. as per the first query</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