Time card reports specifics

[babysilvertooth]

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]

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

[cmcgehee]

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;

[babysilvertooth]

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

[cmcgehee]

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;

[Tom Zaccaria]

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

drtmz

[cmcgehee]

Do you mean that the Date Range column is empty?

[Tom Zaccaria]

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

drtmz

[cmcgehee]

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.

[Tom Zaccaria]

That did it. Perfect.
Very useful freebie!!

drtmz