Joining Tables with aggrigates

I have 3 tables on my db, Projects, ProljectAllocationLog and Users

Project consists of Projectid(PK), ProjectName
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, Users consists of UserID (PK), Fullname

ProjectID ProjectName
1234 ProjectOne
2346 ProjectTwo
7892 ProjectThree

ProjectAllocationLogID ProjectID UserID
1 1234 1
2 2346 3
3 2346 1
4 1234 2
5 7892 2
6 1234 3
7 7892 1
8 7892 2

UserID UserName
1 Debbie Coates
2 Fred Bloggs
3 Jack Smith

I want to be able to view a list of showing the most current User for each project

eg

ProjectID UserName
1234 Jack Smith
2346 DebbieCoates
7892 Fred Bloggs

SELECT Max(ProjectAllocationLog.projectAllocationLogID) AS MaxOfprojectAllocationLogID, ProjectAllocationLog.ProjectID
INTO #TEMP
FROM ProjectAllocationLog
GROUP BY ProjectAllocationLog.ProjectID;

SELECT [#TEMP].ProjectID, ProjectAllocationLog.UserID, Users.UserName
INTO #TEMP2
FROM Users INNER JOIN ([#TEMP] INNER JOIN ProjectAllocationLog ON [#TEMP].MaxOfprojectAllocationLogID = ProjectAllocationLog.projectAllocationLogID) ON Users.Userid = ProjectAllocationLog.UserID;

Select * from #Temp2

I have created this, which shows me the results I want, but think it is really clumsy, Is there a way of doing this so that I dont need to create the Temp tables?SELECT P.ProjectID
, PA.UserID
, U.UserName
FROM Projects as P
INNER
JOIN ProjectAllocationLog as PA
ON PA.ProjectID = P.ProjectID
AND PA.ProjectAllocationLogID =
( SELECT MAX(projectAllocationLogID)
FROM ProjectAllocationLog
WHERE ProjectID = P.ProjectID )
INNER
JOIN Users as U
ON U.Userid = PA.UserID

JOIN, GROUP BY, or HAVING question

Consider the following two tables:

ProjectHours
hoursID {primary key}
employeeID {foreign key}
ProjectDate
ProjectID
ProjectHours

DataComplete
DataCompleteID {primary key}
employeeID {foreign key}
CompleteDate

The first table should be self-explanatory. The second table is there to let me know that a particular employee has entered all of the data for a given date. This tells me that I can include this data in reports and charts.

Here's where I'm having troubles ...

I want to calculate the average hours worked by an employee on a project during a given time period. For example, what is the average number of hours John worked on Project X during the past week? The tricky part is that the employee/date combo must also be found in the
DataComplete table.

Problem: The following SQL statement averages ALL of the data even for dates NOT included in the DataComplete table:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
JOIN
DataComplete d
ON
h.employeeID = d.employeeID
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

How do I get aggregate info only for dates found in the DataComplete table?I see you have a primary key on your ProjectHours table, but what is the NATURAL key?

Do employeeID, ProjectDate, and ProjectID constitute a unique records? If so, try this:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
INNER JOIN DataComplete d
ON h.employeeID = d.employeeID
AND h.ProjectDate = d.CompleteDate
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

If this is not the case, I suspect you will need to modify (normalize) your table design to do what you want to do.

blindman