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.