Sum worked hours

I have a issues table where users can log worked hours and estimate hours that looks like this

id |  assignee  |  task   |   timespent  |  original_estimate | date
--------------------------------------------------------------------------
1  |  john      |  design |   2          |  3                 | 2013-01-01
2  |  john      |  mockup |   2          |  3                 | 2013-01-02
3  |  john      |  design |   2          |  3                 | 2013-01-01
4  |  rick      |  mockup |   5          |  4                 | 2013-01-04

And I need to sum and group the worked and estimated hours by task and date to get this

assignee  |  task   | total_spent   | total_estimate  |  date
------------------------------------------------------------------
john      |  design | 4             | 6               | 2013-01-01
john      |  mockup | 2             | 3               | 2013-01-02
rick      |  design | 5             | 4               | 2013-01-04

Ok, this is easy, I've already got this:

SELECT assignee, task, SUM(timespent) as total_spent, SUM(original_estimate) AS total_estimate, date FROM issues GROUP BY assignee, task, date

My problem is I need to also show the assignees that did not logged hours on any task that day, I mean:

assignee  |  task   | total_spent   | total_estimate  |  date
------------------------------------------------------------------
john      |  design | 4             | 6               | 2013-01-01
john      |  mockup | 2             | 3               | 2013-01-02
rick      |  design | 5             | 4               | 2013-01-04
pete      |  design | 0             | 0               | 2013-01-01
pete      |  mockup | 0             | 0               | 2013-01-02
liz       |  design | 0             | 0               | 2013-01-04
liz       |  mockup | 0             | 0               | 2013-01-04

The goal is to draw a chart like this http://jsfiddle.net/uUjst/embedded/result/

Answers


Assuming that you have a user table, but not a tasks or dates table... meaning that we have to derive these values from the values present in issues:

;WITH dates AS (
    SELECT DISTINCT date
    FROM issues
), tasks AS (
    SELECT DISTINCT task
    FROM issues
)
SELECT 
    u.user as assignee, 
    t.task, 
    SUM(i.timespent) as total_spent, 
    SUM(i.original_estimate) AS total_estimate, 
    d.date 
FROM 
    users u CROSS JOIN
    dates d CROSS JOIN
    tasks t LEFT OUTER JOIN
    issues i ON
        i.assignee = u.user
        AND i.task = t.task
        AND i.date = d.date
GROUP BY u.user, t.task, d.date

You need the Assignees in their own separate table to join from.

SELECT  tblAssignee.Name, task, SUM(timespent) as total_spent, SUM(original_estimate) AS   total_estimate, date 
FROM tblAssignee
    LEFT JOIN issue ON issues.assignee = tblAssignee.Name
GROUP BY tblAssignee.Name, task, date

SELECT
   A.name,
   task,
    ISNULL(SUM(timespent), 0) as total_spent,
    ISNULL(SUM(original_estimate), 0) AS total_estimate,
    date
FROM Assignee A
    LEFT JOIN issue
        ON issues.assignee = A.Name
GROUP BY A.name, task, date

Need Your Help

UISlider does not appear in the UIToolbar

ios objective-c interface-builder uitoolbar

I have a fairly basic video player I'm creating. There is a UIToolbar (toolBar) that contains several buttons and a UISlider. The slider will appear just fine when the toolBar is not a referencing

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.