Open Dental Forum

Noticed recently that there is a glitch in query 538, below, 746 and 756.
If the employee does not work any regular hours in the time period searched they are ignored in the queries. In two cases we had an employee not work but were given adjustment hours, one for maternity and one for a honeymoon, and they were excluded from the totals. I caught it because I knew we were giving them hours.
Anyone know how to fix this. Other than that all three queries seem to work as advertised.

drtmz

/*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
ORDER BY LName, FName;

I've taken a long look and this query expects there to be clock events for this person during the pay period if adjustments are added. If there are no clock events then the adjustments don't get counted either. That dependency is deeply embedded into the query and would take quite some time to get out while still maintaining current functionality. One thing that works is to make a clock event for that employee for zero time; just clock in and out quickly, then the adjustment time would show up alongside zero raw hours. Or better yet when you want to make these adjustment hours for this person, make a clock in/out event then go into their timecard and change the Clock Out Date and Time (Displayed field) for that entry to how many hours you want.

That's what I thought would have to happen. We have to pretend they were in.
Will clock them in and out before the adjustment.

Thank you,
drtmz