0

I have the following tables:

Project
Task
Note
Employee

Each Project has one or more tasks. Each task has one or more notes. Each employee has one or more notes. Each table has its own ID and an ID for the related record, i.e.:

Project.ID
    Task.ID
    Task.ID_Project
        Note.ID
        Note.ID_Task
        Note.ID_Employee
        Note.Duration
Employee.ID

Additionally Note has a Note.Duration field which is a real number specifying the amount of time the given task was worked on for that given note.

Now what I want (or rather the information requested of me) is a table in Excel with the list of projects as row headings and the list of employees as column headings, each cell containing the total amount of time each employee has worked on the given project. I'm not sure how I go about constructing such a query in SQL Server (2008). I started with an aggregate query for a given project such as:

SELECT  Employee.ID, 
        InnerNotes.Duration
        FROM 
        Employee
        LEFT OUTER JOIN
        (SELECT Employee.ID,
            SUM(Note.Duration) AS Duration
            FROM
            Employee
            INNER JOIN
            Note
            ON
            Note.ID_Employee = Employee.ID
            INNER JOIN
            Task
            ON
            Note.ID_Task = Task.ID
            INNER JOIN
            Project
            ON
            Task.ID_Project = Project.ID                
            WHERE
            Project.ID = N'8BC3447F-441D-4E73-B76B-C1304F36841D'
            GROUP BY
            Employee.ID) 
            AS 
            InnerNotes
            ON
            InnerNotes.ID = Employee.ID 

So this query will give me a set of rows with the Employee.ID and the total duration worked on for project 8BC3447F-441D-4E73-B76B-C1304F36841D. What I really want is the total duration worked on for any project for all employees, more like:

                    Bob     Fred    Jim
Widget Making 1     1       2       5
Widget Making 2     -       4       -
Widget Making 3     2       -       -

I'm not a DBA so don't have much experience with SQL. Can someone assist me here please?

Edit: I'm using SQL Server 2012, by the way.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Robinson
  • 9,666
  • 16
  • 71
  • 115

0 Answers0