Open Dental Forum

Hi All,
Is there a way to print a report for an employees time card for the year for each pay period?
Or do I have to choose it from each pay period separately?

Just thought there may be a way to run a report of such. I want to be able to quickly look at the report and see the number of hours worked each week/payperiod to keep track of totally weekly hours/ (i.e.: make sure employees are working 32 hours or who is worked more or less than that in case I have to ask them to work more or less hours for whatever reason).

Thanks

Hello!

Are you familiar with our query examples page? The link below will take you to a list or reports we have made for other customers and you may be able to find a pre-existing one that meets your needs.
http://opendentalsoft.com:1942/ODQueryList/QueryList.as

If you can't find one that works for you there you can always submit a query request as well!
http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx

Just the other day I wrote a query that will display the hours worked each week for every employee. Enjoy!

/*Weekly hours worked for all Employees. First day of the week is Sunday.*/
/*Query code written/modified: 08/02/2016*/
SET @FromDate='2016-01-01' , @ToDate='2016-07-31';
SELECT CONCAT(e.LName,', ',e.FName) AS 'Employee',
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(TimeDisplayed2, TimeDisplayed1),0)))/3600,2) AS 'HoursWorked',
CONCAT(DATE_FORMAT(GREATEST(DATE_SUB(dates.SelectedDate,INTERVAL DAYOFWEEK(dates.SelectedDate)-1 DAY),@FromDate),'%m/%d/%Y'),' - ',
DATE_FORMAT(LEAST(DATE_SUB(dates.SelectedDate,INTERVAL DAYOFWEEK(dates.SelectedDate)-1 DAY)+INTERVAL 6 DAY,@ToDate),'%m/%d/%Y')) AS '_Date Range'
FROM (
SELECT SelectedDate FROM
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) SelectedDate FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SelectedDate BETWEEN @FromDate AND @ToDate
) dates
INNER JOIN employee e ON TRUE
LEFT JOIN clockEvent c ON DATE(c.TimeDisplayed1)=dates.SelectedDate
AND c.EmployeeNum=e.EmployeeNum
GROUP BY YEARWEEK(dates.SelectedDate), e.EmployeeNum
ORDER BY YEARWEEK(dates.SelectedDate),LName, FName;

Chris,
Can I type in the employee name if I want just a specific person?

Yeah sure, that's an easy modification.

/*Weekly hours worked for all Employees. First day of the week is Sunday.*/
/*Query code written/modified: 04/16/2018*/
SET @FromDate='2016-01-01' , @ToDate='2016-07-31';
SET @EmployeeFirstName='Alan';
SET @EmployeeLastName='Turing';
SELECT CONCAT(e.LName,', ',e.FName) AS 'Employee',
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(TimeDisplayed2, TimeDisplayed1),0)))/3600,2) AS 'HoursWorked',
CONCAT(DATE_FORMAT(GREATEST(DATE_SUB(dates.SelectedDate,INTERVAL DAYOFWEEK(dates.SelectedDate)-1 DAY),@FromDate),'%m/%d/%Y'),' - ',
DATE_FORMAT(LEAST(DATE_SUB(dates.SelectedDate,INTERVAL DAYOFWEEK(dates.SelectedDate)-1 DAY)+INTERVAL 6 DAY,@ToDate),'%m/%d/%Y')) AS '_Date Range'
FROM (
SELECT SelectedDate FROM
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) SelectedDate FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE SelectedDate BETWEEN @FromDate AND @ToDate
) dates
INNER JOIN employee e ON TRUE
LEFT JOIN clockEvent c ON DATE(c.TimeDisplayed1)=dates.SelectedDate
AND c.EmployeeNum=e.EmployeeNum
WHERE e.FName LIKE @EmployeeFirstName
AND e.LName LIKE @EmployeeLastName
GROUP BY YEARWEEK(dates.SelectedDate), e.EmployeeNum
ORDER BY YEARWEEK(dates.SelectedDate),LName, FName;

It seems to work but the daterange field doesn't populate. It only returns employee and hours.

drtmz

Do you mean that the Date Range column is empty?

Yes. No data in the daterange column.
At least on my version 17.4.50

drtmz

Oh I see why that's happening. Whenever a column name starts with 'Date', Open Dental will try to format the entire column as a date. I edited my previous post to get around this feature.

That did it. Perfect.
Very useful freebie!!

drtmz