Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    Posts
    8

    Unanswered: 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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •