Adding to query

[wanzek1]

I have a standard report that I would like to update but am struggling getting it to work without errors. I need to add this query: SELECT
FilteredFieldLogEmployee.Field_Training_Custom
,FilteredFieldLogEmployee.Injured_Today_Custom
,FilteredFieldLogEmployee.Holiday_Custom
,FilteredFieldLogEmployee.No_Show_Custom
,FilteredFieldLogEmployee.Per_Diem_Code_Custom
,FilteredFieldLogEmployee.PTO_Custom
,FilteredFieldLogEmployee.Type_of_Leave_Custom
,FilteredFieldLogEmployee.Unpaid_Leave_Custom
,FilteredFieldLogEmployee.FieldLog_FieldLogID
FROM
FilteredFieldLogEmployee
LEFT OUTER JOIN FilteredFieldLogEmployeeHoursAll
ON FilteredFieldLogEmployee.EmployeeREF = FilteredFieldLogEmployeeHoursAll.EmployeeREF AND FilteredFieldLogEmployee.FieldLog_FieldLogID = FilteredFieldLogEmployeeHoursAll.FieldLog_FieldLogID

To the standard query. When I do it doesn't add anything to the report fields.

Here is the query:

/*
-- diagnostics
declare @Foreman UNIQUEIDENTIFIER --NOTEiagnostic
declare @BusinessUnit nvarchar(99)--NOTEiagnostic
declare @EmployeeREF UNIQUEIDENTIFIER
declare @AccountType nvarchar(256)
set @AccountType = '(All)'
declare @StartDate DateTime
set @StartDate = '10/18/2014'
declare @EndDate DateTime
set @EndDate = '10/19/2014'
*/

-- Employee Weekly MainDataSet
-- Note: Copy parameters to local variables as workaround to RS performance bug
declare @_BusinessUnit nvarchar(max)
set @_BusinessUnit = @BusinessUnit

declare @_Foreman nvarchar(max)
set @_Foreman = @Foreman

declare @_Employee nvarchar(max)
set @_Employee = @EmployeeREF

-- Table to hold the overall summary totals for Cost and Quantity in the FL Production Accounts
DECLARE @EmployeeTable TABLE
(
EmployeeREF UniqueIdentifier,
LastName NVARCHAR(100),
FirstName NVARCHAR(100),
MiddleInitial NVARCHAR(100),
Nickname NVARCHAR(100),
EmployeeID NVARCHAR(100),
FieldLogID NVARCHAR(27),
WorkDay INT,
--AccountDescription NVARCHAR(100),
RegularHours FLOAT,
OvertimeHours FLOAT,
DoubleTimeHours FLOAT,
TotalHours FLOAT
)
INSERT INTO @EmployeeTable
SELECT
FilteredEmployee.ObjectID AS EmployeeREF,
FilteredEmployee.LastName,
FilteredEmployee.FirstName,
FilteredEmployee.MiddleInitial,
FilteredEmployee.Nickname,
FilteredEmployee.EmployeeID,
Max(FilteredFieldLogEmployeeHoursAll.FieldLog_FieldLogID),
WorkDay = DATEDIFF( DAY, @StartDate, MAX(FilteredFieldLogEmployeeHoursAll.FieldLog_WorkStartDateTime) ),
/* CASE Max(FilteredFieldLogEmployeeHoursAll.AccountType)
WHEN '1' THEN /* Overhead */
Max(FilteredFieldLogEmployeeHoursAll.JobAccount_Description)
WHEN '2' THEN /* Production */
'Production Hours'
ELSE
'#Error'
END AS AccountDescription,
*/
SUM(FilteredFieldLogEmployeeHoursAll.RegularHours) AS RegularHours,
SUM(FilteredFieldLogEmployeeHoursAll.OvertimeHours) AS OvertimeHours,
SUM(FilteredFieldLogEmployeeHoursAll.DoubleTimeHours) AS DoubleTimeHours,
SUM(FilteredFieldLogEmployeeHoursAll.TotalHours) AS TotalHours

FROM FilteredFieldLogEmployeeHoursAll

INNER JOIN FilteredEmployee
ON FilteredEmployee.ObjectID = FilteredFieldLogEmployeeHoursAll.EmployeeREF

INNER JOIN FilteredFieldLog
ON FilteredFieldLog.ObjectID = FilteredFieldLogEmployeeHoursAll.FieldLogREF

WHERE(
FilteredFieldLog.WorkStartDateTime between @StartDate and @EndDate + 1
AND FilteredFieldLog.Status NOT IN (1, 4) -- Do not include Draft(1) or Rejected(4) Field Logs.
AND ( ( @_BusinessUnit IS NULL ) OR ( FilteredFieldLog.BusinessUnitREF = @_BusinessUnit ) )
AND ( ( @_Foreman IS NULL ) OR ( FilteredFieldLog.ForemanREF = @_Foreman) )
AND ( ( @_Employee IS NULL ) OR ( FilteredEmployee.ObjectID = @_Employee) )
AND ( '(All)' IN (@AccountType)
OR
('Overhead' IN (@AccountType) AND AccountType = 1)
OR
('Production' IN (@AccountType) AND (AccountType = 2 AND JobAccount_IsTnMAccount = 0))
OR
('T&M' IN (@AccountType) AND (AccountType = 2 AND JobAccount_IsTnMAccount = 1))
)
AND ( FilteredFieldLogEmployeeHoursAll.TotalHours <> 0 )
)

GROUP BY
FilteredEmployee.ObjectID,
FilteredEmployee.LastName,
FilteredEmployee.FirstName,
FilteredEmployee.EmployeeID,
FilteredEmployee.MiddleInitial,
FilteredEmployee.Nickname,
FilteredFieldLog.WorkStartDateTime

ORDER BY
WorkDay,
FilteredEmployee.LastName,
FilteredEmployee.FirstName,
FilteredEmployee.MiddleInitial,
FilteredEmployee.Nickname,
FilteredEmployee.EmployeeID


--SELECT * FROM @EmployeeTable

--Order BY WorkDay,
-- LastName,
-- FirstName,
-- MiddleInitial,
-- Nickname

select
EmployeeREF as EmployeeREF,
rtrim(LastName) as LastName,
rtrim(FirstName) as FirstName,
rtrim(MiddleInitial) as MiddleInitial,
rtrim(Nickname) as Nickname,
EmployeeID,
(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 0) as '0_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 0) as '0_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 0) as '0_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 0) as '0_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 1) as '1_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 1) as '1_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 1) as '1_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 1) as '1_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 2) as '2_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 2) as '2_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 2) as '2_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 2) as '2_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 3) as '3_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 3) as '3_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 3) as '3_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 3) as '3_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 4) as '4_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 4) as '4_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 4) as '4_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 4) as '4_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 5) as '5_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 5) as '5_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 5) as '5_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 5) as '5_Total',

(select Coalesce(SUM(Coalesce(RegularHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 6) as '6_RT',
(select Coalesce(SUM(Coalesce(OvertimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 6) as '6_OT',
(select Coalesce(SUM(Coalesce(DoubleTimeHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 6) as '6_DT',
(select Coalesce(SUM(Coalesce(TotalHours,0)),0) FROM @EmployeeTable where EmployeeREF = et.EmployeeREF and WorkDay = 6) as '6_Total'

FROM
@EmployeeTable et

Group by
LastName,
FirstName,
MiddleInitial,
Nickname,
EmployeeID,
EmployeeREF

Order By
LastName,
FirstName,
MiddleInitial,
Nickname,
EmployeeID