The fields you have in your GROUP BY should be in your SELECT statement. HOMELABORLEVELNM4 is in the group by but not in the SELECT. It’s substringed, but the whole column is not selected.
The top portion should be
SELECT EmployeeId 'EmpId'
, LaborLevelName4 'Plant'
, LaborLevelDsc3 'DeptName'
, LaborLevelName6 'Employeetype'
, PERSONFULLNAME 'Employee'
, SUBSTRING(HOMELABORLEVELNM4,1,6) 'Dept'
, SUBSTRING(HOMELABORLEVELNM4,7,1) 'SHIFT1'
, ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours'
, ROUND(SUM(WageAmount),2) 'Earnings'
FROM Totals
WHERE ApplyDate BETWEEN @from_timeframe AND @to_timeframe
AND PayCodeName = 'Regular'
GROUP BY EmployeeId
, LaborLevelName4
, LaborLevelDsc3
, LaborLevelName6
, PERSONFULLNAME
, SUBSTRING(HOMELABORLEVELNM4,1,6)
, SUBSTRING(HOMELABORLEVELNM4,7,1)
It got resolved .Thank you for @DaveMaxwell for helping to brainstorm the issue . It was resolved by changing the line as:
substring(laborlevelname4,1,6) “Dept”,
substring(laborlevelname4,7,1) “Shift”,