USE [Payroll]
GO
/****** Object:  StoredProcedure [dbo].[EmployeeProfitability]          Script Date: 9/23/2018 7:34:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER PROCEDURE [dbo].[EmployeeProfitability]
@PayrollDate DateTime

AS
BEGIN

SELECT  0 AS Id, FirstName, LastName,
    Volume.Volume AS Volume,
    Volume.MaterialPct AS MaterialPct,
    Volume.LaborPct AS LaborPct,
    Volume.ProfitPct AS ProfitPct,
    Volume.Systems AS Systems,
    Volume.AOR AS AOR,
    Volume.Upgrade AS Upgrade,
    Volume.Condemned AS Condemned,
    Volume.TravelTime AS TravelTime,
    Volume.RegularHours AS RegularHours,
    Volume.OvertimeHours AS OvertimeHours
    from dbo.Employees e
LEFT JOIN (
    SELECT EmployeeId,
    SUM(Billing) AS Volume,
    SUM(Billing) / SUM(MaterialCost) AS MaterialPct,
    SUM(Billing) / SUM(Pay) AS LaborPct,
    SUM(Billing) / SUM(Profit) AS ProfitPct,
    SUM(System) AS Systems,
    SUM(AOR) AS AOR,
    SUM(Upgrade) AS Upgrade,
    SUM(Condemned) AS Condemned,
    SUM(TravelMinutes) AS TravelTime,
    SUM(RegularHours) AS RegularHours,
    SUM(OvertimeHours) AS OvertimeHours
    FROM dbo.EmployeeJobs ej
    WHERE PayrollDate = @PayrollDate
    GROUP BY ej.EmployeeId
) AS Volume ON
e.EmployeeId = Volume.EmployeeId
END