Time card reports specifics

For users or potential users.
Post Reply
babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Time card reports specifics

Post by babysilvertooth » Mon Sep 05, 2016 11:11 am

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

JoeMontano
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: Time card reports specifics

Post by JoeMontano » Tue Sep 06, 2016 7:55 am

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

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Time card reports specifics

Post by cmcgehee » Wed Sep 07, 2016 9:39 am

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;
Last edited by cmcgehee on Wed Apr 18, 2018 8:00 am, edited 1 time in total.
Chris McGehee
Open Dental Software
http://www.opendental.com

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: Time card reports specifics

Post by babysilvertooth » Mon Apr 16, 2018 9:54 am

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

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Time card reports specifics

Post by cmcgehee » Mon Apr 16, 2018 12:40 pm

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;
Last edited by cmcgehee on Wed Apr 18, 2018 8:00 am, edited 1 time in total.
Chris McGehee
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Time card reports specifics

Post by Tom Zaccaria » Tue Apr 17, 2018 12:49 pm

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

drtmz

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Time card reports specifics

Post by cmcgehee » Tue Apr 17, 2018 1:20 pm

Do you mean that the Date Range column is empty?
Chris McGehee
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Time card reports specifics

Post by Tom Zaccaria » Wed Apr 18, 2018 3:06 am

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

drtmz

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Time card reports specifics

Post by cmcgehee » Wed Apr 18, 2018 8:00 am

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.
Chris McGehee
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Time card reports specifics

Post by Tom Zaccaria » Wed Apr 18, 2018 11:18 am

That did it. Perfect.
Very useful freebie!!

drtmz

Post Reply