Monthly or yearly staff hours??

For users or potential users.
Post Reply
aussiedentist
Posts: 112
Joined: Sat Jan 22, 2011 7:33 pm

Monthly or yearly staff hours??

Post by aussiedentist » Wed Apr 05, 2017 4:00 am

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

User avatar
JasonL
Posts: 28
Joined: Wed Jun 10, 2015 1:57 pm

Re: Monthly or yearly staff hours??

Post by JasonL » Wed Apr 05, 2017 1:16 pm

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! :-)
Jason Long
Open Dental Web Technology Coordinator
www.opendental.com

jbrester
Posts: 2
Joined: Fri Apr 28, 2017 2:44 pm

Re: Monthly or yearly staff hours??

Post by jbrester » Sat Apr 29, 2017 11:41 am

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
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: Monthly or yearly staff hours??

Post by JoeMontano » Mon May 01, 2017 8:55 am

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.
Joe Montaño
Software Engineer
Open Dental Software
Joe@OpenDental.com

Post Reply