Help with Subquery and Having Clause ..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Subquery and Having Clause ..

Author

Topic

wco5002
Starting Member

10 Posts

Posted - 2008-04-28 : 16:06:16

Hey, I'm referencing a database about a fictitious IT company, and I'm trying to answer the following statement:

Determine who has logged the most hours. Create a SELECT statement to return their name and the max hours they have worked. NOTE: This is a tougher one but looking into using a Subquery and use the HAVING statement.

I've written this so far, and although it does give me the right answer (there is only one employee in the DB who has worked on projects) it is not entirely correct. Basically there is no subquery, and it doesn't really show who has worked the max hours:

SELECT 
  EMPLOYEE_NAME 'Employee Name', SUM(HOURS_LOGGED) 'Maximum Hours Logged'
FROM 
  PROJECT_EMPLOYEE PE
INNER JOIN 
  PROJECT_HOURS PH ON PH.EMPLOYEE_ID = PE.EMPLOYEE_ID
INNER JOIN 
  PROJECT P ON P.PROJECT_ID = PH.PROJECT_ID
GROUP BY 
  PE.EMPLOYEE_NAME

Here is the DB if needed:

CREATE TABLE project_employee (
  employee_id               INT,
  employee_name             VARCHAR(40),
  employee_hire_date        DATETIME,
  employee_termination_date DATETIME,
  employee_billing_rate     INT,
  CONSTRAINT project_employee_pk
    PRIMARY KEY (employee_id)
);

CREATE TABLE project (
  project_id              INT,
  project_name            VARCHAR(40),
  project_budget          INT,
  CONSTRAINT project_pk
    PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
  project_id              INT,
  employee_id             INT,
  time_log_date           DATETIME,
  hours_logged           INT,
  dollars_charged         INT,
  CONSTRAINT project_hours_pk
    PRIMARY KEY (project_id, employee_id, time_log_date),
  CONSTRAINT proj_hours_fkto_projectemployee
    FOREIGN KEY (employee_id) REFERENCES project_employee,
  CONSTRAINT proj_hours_fkto_project
    FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1005, 'TCP/IP Implementation',415000);



INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);


INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-DEC-1998',3.00,507.00);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 23:58:29

[code]
SELECT TOP 1 pe.employee_name,SUM(hours_logged)
FROM project p
INNER JOIN project_hours ph
ON ph.project_id=p.project_id
INNER JOIN project_employee pe
ON pe.employee_id=ph.employee_id
GROUP BY pe.employee_name
ORDER BY SUM(hours_logged) DESC[/code]

wco5002
Starting Member

10 Posts

Posted - 2008-04-29 : 01:06:25

Thanks for the help !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources