Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the first view displayed as:

CREATE VIEW Projects AS
    SELECT DepartmentName, 
        COUNT(*)NumberOfProjects,
        OfficeNumber,
        Phone
    FROM   DEPARTMENT AS D JOIN PROJECT AS P
            ON D.DepartmentName = P.Department

    GROUP BY Department;

However, I want to display a "NumberOfOverBudgetProjects" for each department in this view. I created a view which lists which projects are going over their hourly budget:

CREATE VIEW Budgets AS
    SELECT Department, 
        MaxHours AS ProjectMaxHours, SUM(HoursWorked) AS TotalHoursWorked,
        SUM(HoursWorked) - MaxHours AS Balance
    FROM   PROJECT AS P JOIN ASSIGNMENT AS A
        ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID

;

Marketing department should list 2 projects being over budget and Accounting and Finance should both list 0.

Any ideas how I can incorporate a column of "OverBudgetProjects" in the original view? Or by creating a new view?

share|improve this question
    
Please don't cross post your question stackoverflow.com/q/19041847/1920232 –  peterm Sep 27 '13 at 5:54
    
Don't cross post between sites. –  JNK Sep 27 '13 at 15:49
add comment

closed as off-topic by JNK Sep 27 '13 at 15:49

If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

I am using SQL Server 2012, but this, or something close, should work in MySQL:

USE Test;
GO
CREATE TABLE Departments
(
    Department NVARCHAR(255)
);
CREATE TABLE Projects
(
    Department NVARCHAR(255)
    , Project NVARCHAR(255)
    , MaxHours INT
);
CREATE TABLE Assignments
(
    Project NVARCHAR(255)
    , HoursWorked INT
);
GO
CREATE VIEW Budgets AS
    SELECT ProjectInfo.Department
        , COUNT(ProjectInfo.Project) AS NumberOfProjects
        , SUM(Projectinfo.OverBudget) AS NumberOfOverbudgetProjects
    FROM (
        SELECT D.Department
            , P.Project
            , P.MaxHours AS ProjectMaxHours
            , SUM(HoursWorked) AS TotalHoursWorked
            , CASE WHEN (SUM(HoursWorked) > P.MaxHours) THEN 1 ElSE 0 END AS OverBudget
        FROM   Departments AS D
            INNER JOIN Projects AS P ON D.Department = P.Department 
            INNER JOIN Assignments AS A ON P.Project = A.Project
        GROUP BY D.Department
            , P.Project
            , P.MaxHours
        ) AS ProjectInfo
    GROUP BY ProjectInfo.Department;
GO
INSERT INTO Departments (Department) VALUES ('Marketing');
INSERT INTO Departments (Department) VALUES ('Sales');
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject1', 10);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject2', 20);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject1', 15);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject2', 25);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('SalesProject1', 14);
SELECT * FROM Budgets;

This returns the folloowing resultset, showing only the Marketing Department, which is over budget.

enter image description here

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.