I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:
1. Write the main query with rows: project identifier, task subject and total hours in a date range.
SELECT p.identifier, e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours FROM redmine_nsp.time_entries e INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id INNER JOIN redmine_nsp.projects p ON e.project_id = p.id WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07' GROUP BY[Read more...]