Monthly or yearly staff hours??

[aussiedentist]

I am looking at staff hours and trying to figure out if I am overstaffed

Is there a way in which od can give me a monthly, or quarterly or yearly breakdown on staffing hours per employee??

I would like to relate this to production and see any trends, particularly as I have now put on a hygeinist and my costs are escalating

[JasonL]

I'm not 100% on whether or not this is what you're looking for, but this may help provide some assistance to you. On our Query Examples page I found #756 "Time Card Summary for all Employees with days worked and avg hours per day". It allows you to set a date range as well. For ease of access I've included it in this post.

Code: Select all

/*756 Time Card summary for all Employees with days worked and avg hours per day*/
SET @FromDate='2013-10-29', @ToDate='2013-11-11';
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*/,
daysworked.Days AS 'DaysWorked',
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)
	+
	((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))
/daysworked.Days,2) AS AvgHoursPerDay
FROM employee e
INNER JOIN clockEvent c ON c.EmployeeNum=e.EmployeeNum
INNER JOIN (
	SELECT c.EmployeeNum,COUNT(*) AS Days
	FROM (
		SELECT DISTINCT c.EmployeeNum,DATE(c.TimeDisplayed1)
		FROM clockEvent c
		WHERE c.ClockStatus IN(0,1)
		AND DATE(c.TimeDisplayed1) BETWEEN @FromDate AND @ToDate
	) c
	GROUP BY EmployeeNum
) daysworked ON daysworked.EmployeeNum=e.EmployeeNum
WHERE (DATE(c.TimeDisplayed1) BETWEEN @FromDate AND @ToDate)
GROUP BY c.EmployeeNum;

Hope this helps!

[jbrester]

I have been using this same query but have found it to be not correct. I have one employee who has taken several days off and another who hasn't missed a single day. They both have the same amount of days worked in this query. They are both full time employees. Can you double check the code as I like this report but can't rely on it as I know it is not correct.

Jason

[JoeMontano]

I recommend taking a peak at our query process we offer if you are not familiar.
http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx

After you submit an initial request above we will contact you with any questions or clarifications we might need and provide a quote for free soon there after. We also offer 1,100+ queries that we have created previously for our customers that we then make available for free to everyone.

Hope this helps.