Query #746 variation

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Query #746 variation

Post by Tom Zaccaria » Fri Nov 08, 2013 4:02 am

Query # 746 calculates RawHours or clock hours employees work.
Considering hygiene only, can anyone direct me to a query that will calculate the time a clocked in hygienist does not or did not have a patient scheduled?

For example, she was clocked in all morning but did not have a patient scheduled from 10:00 to 11:00, or had a cancellation or now show at some time during the day.

Thanks,
drtmz

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Query #746 variation

Post by Mifa » Mon Nov 11, 2013 10:57 am

Here is a variation of query 538 I'm using to track paid hours vs. production hours for any given period of time. Code can certainly be improved. Patnum 4031 and 4551 are "special" patients we use to write notes in the agenda (something like a postit), I don't want to count their "appointments" as production; change/remove these conditions to fit your setup. Hope this helps!

/*538 Time Card summary for all Employees.*/
SET @FromDate='2013-02-11' , @ToDate='2013-02-17';

drop table if exists tmp;

create table tmp
SELECT e.EmployeeNum,
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*/
FROM employee e
INNER JOIN clockEvent c ON c.EmployeeNum=e.EmployeeNum
WHERE (c.TimeDisplayed1 BETWEEN @FromDate AND @ToDate)
GROUP BY c.EmployeeNum;

SELECT
provider.ProvNum,
sum(char_length(appointment.Pattern))*5/60 AS Production Hours',
tmp.RegHours as 'Paid Hours'
FROM
provider, appointment, tmp, employee
WHERE
appointment.ProvHyg = provider.ProvNum AND

appointment.AptStatus = '2' AND
appointment.PatNum <> '4031' AND
appointment.PatNum <> '4551' AND
appointment.AptDateTime BETWEEN @FromDate AND @ToDate AND
employee.employeenum = tmp.employeenum AND
employee.Lname = provider.lname AND
employee.Fname = provider.Fname
GROUP BY
provider.ProvNum

Post Reply