StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Don't know where Company and Division join...but here this is:</p> <pre><code>select c.name as company, d.name as division, s.name as salesperson, sum(h.callsinbound) as callsinboundsum, sum(h.callsoutbound) as callsoutboundsum, sum(h.issuedorders) as issuedorderssum, sum(h.revenue) as revenuesum from sales_history_performance h inner join (select th.salespersonid, date(th.timestamp) as my_date, max(th.timestamp) as max_time from sales_history_performance th inner join salesperson ts on th.salespersonid = ts.id where th.timestamp between '5/1/2009' and '5/3/2009' --inclusive in MySQL group by th.salespersonid, date(th.timestamp) ) t on h.salespersonid = t.salespersonid and h.timestamp = t.max_time inner join salesperson s on h.salespersonid = s.id inner join division d on s.divisionid = d.id inner join company c on d.companyid = c.id group by c.name, d.name, s.name order by 1,2,3 </code></pre> <p>You can edit the <code>and sp.name like '%'</code> line that's commented out to add whatever sales person filter you need to it.</p> <p>What this does is thusly: It goes out and builds a table of the top timestamp in each day. If ID in <code>sales_history_performance</code> is reliably larger for later entries, use that, since you're less likely to get duplicates. Anyway, then it joins that to the table summing up all of the metric columns, per salesperson. You can take the sales person out of the outer query if you want to get a company wide number. As this were, it will return all sales people.</p> <p><strong>Update</strong>: I added in company and division. This is a pretty generic query. If you'd like to limit on division/company/salesperson, you can do so in the WHERE clause of the outer query, although you may be able to get some performance gains out of doing it in the inner query--it's just a bit harder to maintain.</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