Open Dental Forum

I am trying to get Query #538 Time Card Summary to be ordered by last name but can't seem to find the right spot in the code to get it to work. Can someone with a lot more query knowledge than myself help me with this? Thanks

/*538 Time Card summary for all Employees.*/
SET @FromDate='2011-02-01' , @ToDate='2011-02-15';
SELECT e.FName,e.LName,
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(TimeDisplayed2, TimeDisplayed1),0)))/3600,2) AS RawHours,
FORMAT(
(IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
IFNULL((SELECT SUM(TIME_TO_SEC(ta.OTimeHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj OT hours*/ +
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0))))/3600
,2) AS AdHours,
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(c.TimeDisplayed2)>0 AND c.ClockStatus=2), TIMEDIFF(c.TimeDisplayed2, c.TimeDisplayed1),0)))/3600,2) AS BreakTot,
FORMAT((SUM(TIME_TO_SEC(IF((TIME_TO_SEC(c.TimeDisplayed2)>0 AND c.ClockStatus=2), TIMEDIFF(c.TimeDisplayed2, c.TimeDisplayed1),0)))+
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0))) /*minus (+) adjustments*/
)/3600,2) AS BreakAdj,
FORMAT((IFNULL((SELECT SUM(TIME_TO_SEC(ta.OTimeHours)) FROM timeadjust ta WHERE ta.EmployeeNum =e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj OT hours*/ +
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) AND c.OTimeHours<>'-01:00:00', (c.OTimeHours),0)))+
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),c.OTimeAuto,0))))/3600,2) AS OverTime,
FORMAT((IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum =e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
SUM(TIME_TO_SEC(IF((TIME_TO_SEC(c.TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(c.TimeDisplayed2, c.TimeDisplayed1),0)))+
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0)))- /*minus (+) adjustments*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) AND c.OTimeHours<>'-01:00:00', c.OTimeHours,0)))- /*minus Overtime*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) , c.OTimeAuto,0))))/3600,2) AS RegHours /*minus Overtime Auto*/
FROM employee e
INNER JOIN clockEvent c ON c.EmployeeNum=e.EmployeeNum
WHERE (DATE(c.TimeDisplayed1) BETWEEN @FromDate AND @ToDate)
GROUP BY c.EmployeeNum;

try replacing the last line with this

GROUP BY e.Lname;

drtmz

I tried that but it will then combine employees with the same last name. We have 2 employees with the same last name. Order By e.FName defeats the purpose as I want the report ordered by last name to make it the same as payroll order (which is ordered by last name).

Would

e.Lname, e.Fname;

work???

drtmz

Yes, that worked. Thanks