Exporting all of my employees timesheets

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

Exporting all of my employees timesheets

Post by aussiedentist » Sun Feb 21, 2016 5:36 pm

I have the taxman coming a knocking, and has requested all of my employees time sheets

How can I export all of my employees time sheets into a spreadsheet?, since the beginning of time?

bpcomp
Posts: 304
Joined: Mon Feb 27, 2012 7:30 am
Location: Tucson, AZ
Contact:

Re: Exporting all of my employees timesheets

Post by bpcomp » Tue Feb 23, 2016 7:23 am

Use custom query #756. Adjust the FromDate and ToDate to match the time period you need.

Check out http://www.opendental.com/manual/queryrun.html to help you if you haven't run a custom query.

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;

aussiedentist
Posts: 112
Joined: Sat Jan 22, 2011 7:33 pm

Re: Exporting all of my employees timesheets

Post by aussiedentist » Tue Feb 23, 2016 1:21 pm

Thanks for the reply.

All i got was the first line, saying Fname, lname, ras hours, etc aup to avg hrs per day,

but no actual data at all!!

I am using the australian version, ultimo dental. Could this be the reason why?

Any ideas as to what else i can try?

User avatar
Arna
Posts: 444
Joined: Tue Jul 09, 2013 3:16 pm

Re: Exporting all of my employees timesheets

Post by Arna » Tue Feb 23, 2016 1:32 pm

Change the date ranges. That query was written for 2013
Entropy isn't what it used to be...

Arna Meyer

Post Reply