June 23, 2015 at 9:17 am
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 --NOTE:Diagnostic
declare @BusinessUnit nvarchar(99)--NOTE:Diagnostic
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
(
EmployeeREFUniqueIdentifier,
LastNameNVARCHAR(100),
FirstNameNVARCHAR(100),
MiddleInitialNVARCHAR(100),
NicknameNVARCHAR(100),
EmployeeIDNVARCHAR(100),
FieldLogIDNVARCHAR(27),
WorkDayINT,
--AccountDescriptionNVARCHAR(100),
RegularHoursFLOAT,
OvertimeHoursFLOAT,
DoubleTimeHoursFLOAT,
TotalHoursFLOAT
)
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
June 23, 2015 at 11:51 am
The fields on the report are driven by the final Select query which gets its data from the @EmployeeTbl so you either need to join to that and add the new fields or add the new field to @EmployeeTbl and populate them in the INSERT.
Once you add the columns to the output query, you need to do a Refresh Fields on the report data set.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 12:24 pm
Thanks for the reply. I get what I need to do based on your instructions but I keep getting errors. I must have something incorrect.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply