0

everyone.

I have played with a test database and wanted to calculate the budget (salaries) allocated to each ProjectID.

CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NULL,
[JobTitle] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL,
[ManagerID] [int] NULL,
[HireDate] [smalldatetime] NOT NULL,
[Salary] [money] NOT NULL,
[AddressID] [int] NULL,CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
CREATE TABLE [dbo].[EmployeesProjects](
    [EmployeeID] [int] NOT NULL,
    [ProjectID] [int] NOT NULL,
 CONSTRAINT [PK_EmployeesProjects] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC,
    [ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeesProjects]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesProjects_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO

ALTER TABLE [dbo].[EmployeesProjects] CHECK CONSTRAINT [FK_EmployeesProjects_Employees]
GO

ALTER TABLE [dbo].[EmployeesProjects]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesProjects_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO

ALTER TABLE [dbo].[EmployeesProjects] CHECK CONSTRAINT [FK_EmployeesProjects_Projects]
GO

I have created the below VIEW called Budget:

CREATE VIEW Budget AS (SELECT Employees.EmployeeID, Employees.Salary, EmployeesProjects.ProjectID 
FROM Employees
JOIN EmployeesProjects ON EmployeesProjects.ProjectID = Employees.EmployeeID)

I have found a solution that lists the budget allocated to eac ProjectID:

SELECT SUM(Salary) AS BudgetedSalary, ProjectID FROM Budget
GROUP BY(ProjectID)

My questions to you guys: Is there a way to get the solution in a more elegant way, e.g. with a sub-query instead of using a VIEW (with a single query)?

Thanks much in advance!

p.s. I can add the SQL script for both tables' data insert, if requested...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.