INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Adding to query

Adding to query

(OP)
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
(
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close